Transfer date to access dbase if issue
Hello all
I'm trying to build this code so that it transfer all information into my Microsoft Access dBase.
The problem is with this part of the code where i want to says these:
1) If there is nothing in etape2.Text, i dont want to save DTPicker19 in access.
2) If there is nothing in etape3.Text, i dont want to save DTPicker20 in access.
3) If there is nothing in etape4.Text, i dont want to save DTPicker21 in access.
4) If there is nothing in etape5.Text, i dont want to save DTPicker22 in access.
5) If there is nothing in etape6.Text, i dont want to save DTPicker23 in access.
6) If there is nothing in etape7.Text, i dont want to save DTPicker24 in access.
When i do a test with only the first statement, the error i have is: run time error 2417217900(80040e14)
Syntax error (missing operator) in query expression ‘Description=operator’s current stats”.
Code:
IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")
Can you please help me?
Thanks again
FULL code
Code:
'Insert data in Microsoft Access Data Base
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Form4.base_de_donnee.Text
MyConn.Open
Set MyRecSet1 = MyConn.Execute( _
"INSERT INTO DBase_vignoble_personnalise ([Date_of_day], [Client], [Adresse], [Ville], [Province], [Code_Postal], [Telephone], [Cellulaire], [Courriel], [Permis_Conduire], [Type_Boisson], [No_Lot], [Date_Depart], [Etape2_nom], [Etape2_date], [Etape3_nom], [Etape3_date], [Etape4_nom], [Etape4_date], [Etape5_nom], [Etape5_date], [Etape6_nom], [Etape6_date], [Etape7_nom], [Etape7_date], [Genre_Boisson], [Notes]) VALUES ( '" & _
DTPicker18 & _
"' , '" & ComClient.Text & ", " & ComNom.Text & "' , '" & address.Text & _
"' , '" & ville.Text & "' , '" & province.Text & _
"' , '" & code_postal.Text & "' ,'" & telephone.Text & _
"' , '" & cellulaire.Text & "' ,'" & courriel.Text & "' , '" & permis_conduire.Text & _
"' , '" & ComType_boisson.Text & "' , '" & lot_boisson.Text & "' , '" & DTPicker18 & _
"' , '" & etape2.Text & "' , '" & IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'") & "' , '" & etape3.Text & "' , '" & DTPicker20 & _
"' , '" & etape4.Text & "' , '" & DTPicker21 & "' , '" & etape5.Text & "' , '" & DTPicker22 & _
"' , '" & etape6.Text & "' , '" & DTPicker23 & "' , '" & etape7.Text & "' , '" & DTPicker24 & _
"' , '" & variete_boisson.Text & "' , '" & boisson_note.Text & "' )")
MyConn.Close
Re: Transfer date to access dbase if issue
First thing comes to mind is a confilcting data type.
Code:
IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")
"[Etape2_date]" is quite obviously a string, where as the default property of a DTPicker is of type Date.
To make sure what happens, you can do this:
Code:
dim sql$
sql = "INSERT INTO ... 'put your complete SQL command string as you assemble it in a string first
debug.print sql 'print the string so as you can make sure all elements come in and all quotes are placed properly
MyConn.Execute sql 'execute then
Re: Transfer date to access dbase if issue
Hello WoF
This is what i have has a result:
INSERT INTO DBase_vignoble_personnalise ([Date_of_day], [Client], [Adresse], [Ville], [Province], [Code_Postal], [Telephone], [Cellulaire], [Courriel], [Permis_Conduire], [Type_Boisson], [No_Lot], [Date_Depart], [Etape2_nom], [Etape2_date], [Etape3_nom], [Etape3_date], [Etape4_nom], [Etape4_date], [Etape5_nom], [Etape5_date], [Etape6_nom], [Etape6_date], [Etape7_nom], [Etape7_date], [Genre_Boisson], [Notes]) VALUES ( '2012-01-09' , 'Cruze, John' , 'adress' , 'City' , 'Province' , 'ZIP' ,'tel' , 'cell' ,'email' , '' , 'VIN' , '9999' , '2012-01-09' , 'Etape 2' , '2012-01-10' , 'Etape 3' , '2012-01-11' , 'Etape 4' , '2012-01-12' , '' , '2012-01-07' , '' , '2012-01-07' , '' , '2012-01-07' , 'Shiraz' , 'Note Test' )
Re: Transfer date to access dbase if issue
Copy that, go into Access, hit SQL Query, open those tables, and then try to EXECUTE the query from within Access.
It will stop on the character it first doesn't like, and you can track that down, and move to the next one.
Re: Transfer date to access dbase if issue
Yes. That's a perfect idea.
It comes to my mind that you possibly do not put numeric values in quotes.
And wasn't it that date values have to be included in # like #21-02-2010#?
Re: Transfer date to access dbase if issue
Hello again,
ok, i will test it in access also
I think i did not do the test right the first time.
Really sorry.
I forgot to test that part of the code:
Code:
IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")
So if i test all this:
Code:
'Insert data in Microsoft Access Data Base
Dim sql$
Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Form4.base_de_donnee.Text
MyConn.Open
sql = "INSERT INTO DBase_vignoble_personnalise ([Date_of_day], [Client], [Adresse], [Ville], [Province], [Code_Postal], [Telephone], [Cellulaire], [Courriel], [Permis_Conduire], [Type_Boisson], [No_Lot], [Date_Depart], [Etape2_nom], [Etape2_date], [Etape3_nom], [Etape3_date], [Etape4_nom], [Etape4_date], [Etape5_nom], [Etape5_date], [Etape6_nom], [Etape6_date], [Etape7_nom], [Etape7_date], [Genre_Boisson], [Notes]) VALUES ( '" & _
DTPicker18 & _
"' , '" & ComClient.Text & ", " & ComNom.Text & "' , '" & address.Text & _
"' , '" & ville.Text & "' , '" & province.Text & _
"' , '" & code_postal.Text & "' ,'" & telephone.Text & _
"' , '" & cellulaire.Text & "' ,'" & courriel.Text & "' , '" & permis_conduire.Text & _
"' , '" & ComType_boisson.Text & "' , '" & lot_boisson.Text & "' , '" & DTPicker18 & _
"' , '" & etape2.Text & "' , '" & IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'") & "' , '" & etape3.Text & "' , '" & DTPicker20 & _
"' , '" & etape4.Text & "' , '" & DTPicker21 & "' , '" & etape5.Text & "' , '" & DTPicker22 & _
"' , '" & etape6.Text & "' , '" & DTPicker23 & "' , '" & etape7.Text & "' , '" & DTPicker24 & _
"' , '" & variete_boisson.Text & "' , '" & boisson_note.Text & "' )"
Debug.Print sql 'print the string so as you can make sure all elements come in and all quotes are placed properly
MyConn.Execute sql 'execute then
MyConn.Close
This is the debug.print result:
INSERT INTO DBase_vignoble_personnalise ([Date_of_day], [Client], [Adresse], [Ville], [Province], [Code_Postal], [Telephone], [Cellulaire], [Courriel], [Permis_Conduire], [Type_Boisson], [No_Lot], [Date_Depart], [Etape2_nom], [Etape2_date], [Etape3_nom], [Etape3_date], [Etape4_nom], [Etape4_date], [Etape5_nom], [Etape5_date], [Etape6_nom], [Etape6_date], [Etape7_nom], [Etape7_date], [Genre_Boisson], [Notes]) VALUES ( '2012-01-09' , 'Cruze, John' , 'adress' , 'City' , 'Province' , 'ZIP' ,'tel' , 'cell' ,'email' , '' , 'VIN' , 'lot555' , '2012-01-09' , 'Chaptalisation' , '[Etape2_date]' , 'Fermantation secondaire' , '2012-01-11' , 'Stabilisation et Clarification' , '2012-01-12' , 'Soutirage et Clarification' , '2012-01-13' , 'Embouteillage' , '2012-01-14' , 'Test' , '2012-01-15' , 'Shiraz' , 'Note Filed' )
So i see something here in the "Where" section: '[Etape2_date]'
1 Attachment(s)
Re: Transfer date to access dbase if issue
ok, i just did the test also in Access and here is the issue in the picture in the attachment.
In that field, it is a date format. but i did says that IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")
So if i dont put anything in Etape2, then it should leave it blank. Right?
Re: Transfer date to access dbase if issue
Looks like you need to reformat your IIFstatement a bit.
Code:
IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")
The line above will return [Etape_date] or the value of the DTPicker depending on the existance of an entry in the text field. In one case your SQL statement will not be valid. As you will be trying to insert a field name where a date is expected.
To get around this you need to either use conditional statements to exclude the Field from the list of expected fields in the first part of your insert statement as well as not sending a value in the case where you do not want to save the data or you can insert a null assuming your db will allow it.
Re: Transfer date to access dbase if issue
Hello all
I have found another way to do it. Maybe not the perfect solution but it works.
Ex:
For each DTPicker, when i select, il put's the sate date in a normal textbox.
That way, if i dont enter a test in etape2.Text, then the textbox that is attached to the DTPicker stay blank.
So in the code, i have replaced all DTPicker with textbox.
And now it work.
I will just do another test.
Re: Transfer date to access dbase if issue
Call me a sour prune or an old f@rt, but I am old fashioned. Names like DTPicker1 etc. is not good practise. Names like Expression1 etc. is also bad. Use proper naming conventions for your objects, it makes it much easier to remember then ;)
Re: Transfer date to access dbase if issue
You now what, i undrestand what you are saying and i will work on that, to name them properly.
Thanks for telling me this. :)
