Transfer date to access dbase if issue

[wilder1926]

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”.

   IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")

Can you please help me?

Thanks again



FULL 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

[WoF]

First thing comes to mind is a confilcting data type.

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:

  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

[wilder1926]

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' )

[dglienna]

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.

[WoF]

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#?

[wilder1926]

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:

   IIf(etape2.Text <> "", "[Etape2_date]", "'" & DTPicker19 & "'")

So if i test all this:

 '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]'

[wilder1926]

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?

[DataMiser]

Looks like you need to reformat your IIFstatement a bit.

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.

[wilder1926]

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.

[HanneSThEGreaT]

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

[wilder1926]

You now what, i undrestand what you are saying and i will work on that, to name them properly.

Thanks for telling me this.