Update date in correct format

[danmack]

Hi hope someone can help me with this, I'm trying to pass some dates from an excel userform into access.

The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field in access in the American format.

In access the date fields are set to Short Date and the example shown for this format is in the UK format. I assign the date to a variable before passing that variable to the update SQL string:

s1 = Nz(DTPicker1.Value, #1/1/2000#)

I have dimmed s1 as date and then added:

s1 = Format(Date, "dd/MM/yyyy")

My update string is:

"SET [Stage 1] = " & "#" & s1 & "#" & " "

I suspect that the nozero function may be the issue but am at a bit of loss atm.

Any help much appreciated.

Thanks

Dan

 

[Cronk]

I suspect the problem is in the statement
"SET [Stage 1] = " & "#" & s1 & "#" & " "Where is this code? Access? If so, in which module/process?

 

[danmack]

Hi Cronk,

The code is in an excel module and is part of the update sql string:

s1 is a variable linked to a dtpicker embedded in an excel userform. The date format is dd/MM/yyy. Hope this helps.

Thanks

 

[pr2-eugin]

See if this works..

"SET [Stage 1] = " & Format(Nz(DTPicker1.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & "

 

[danmack]

Hi Paul,

Thanks for your reply but i can't get the code working, keep getting a syntax error, the full string is below and i have tried your code at stage 2 (I don't think this should cause an error?):

Dim SQL As String
 SQL = "Update [Register1]" & _
        "SET [Stage 1] = " & "#" & s1 & "#" & " " & _
        ",[Stage 2] = " & Format(Nz(DTPicker2.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " & _
        ",[Stage 3] = " & "#" & s3 & "#" & " " & _
        ",[Stage 4] = " & "#" & s4 & "#" & " " & _
        ",[Stage 5] = " & "#" & s5 & "#" & " " & _
        ",[Stage 6] = " & "#" & s6 & "#" & " " & _
        ",[Stage 7] = " & "#" & s7 & "#" & " " & _
        ",[Supplier] = """ & Vendor & """ " & _
        ",[Vendor Code] = " & vendorcde & " " & _
        ",[Supply] = """ & domex & """ " & _
        ",[Engine Project] = """ & project & """ " & _
        ",[Root Cause] = """ & Root & """ " & _
        ",[Ownership] = """ & Rooto & """ " & _
        ",[Failure Mode] = """ & failm & """ " & _
        ",[Generic DFI] = """ & GENDFI & """ " & _
        ",[Refers to DFI] = """ & rfrDFI & """ " & _
        ",[Generic Comments] = """ & gencom & """,[CFBU] = """ & CFBU & """,[Customer] = " & Customer & " " & _
        ",[Region] = """ & Region & """ " & _
        ",[Comments] = """ & COMM & """, [Reasons for Deletion] = """ & RFD & """, [Customer Interface Leader] = """ & CIL & """ " & _
        "WHERE ([DFI No] = """ & DFII & """)"

Apologies if i've misunderstood or not sufficiently explained what i'm trying to do.

Thanks again.

Dan

 

[pr2-eugin]

Do the same for all Date fields and try again.. Use the Debug.Print method to check for any Syntax errors..

 

[danmack]

Paul,

I didn't have to use the debugger (Although it would have highlighted the same problem) - just had a focused look at my code.... Appreciate your time and help with this.

Regards

Dan

 

[pr2-eugin]

Okay unclear if the problem is solved or not.. Anyway.. Debug.Print will print the generated SQL in the immediate window, so when the output is generated you will be able to see what is being passed on to the Compiler..

If your eye fails to catch any, copy and paste it into a Query builder's SQL view.. Then it will take you through the errors you have.. That is the use of the Debug.Print statement.. Highlighting is different from using the Debug.Print statement.. The link provided would have explained that..

If you have not been able to put your finger on the problem, try as suggested.. If you have been able to solve, it would be greatly appreciated if you could share it with us, so many would be helped. Thanks.

 

[danmack]

Thanks Paul,

Problem solved, the code in full follows. I think what also helped was removing all of the instances in my excel vba module where i had tried to force the date format...after realising that Access always wants the date passing to it in it's native mm/dd/yyyy.

Dim SQL As String
 SQL = "Update [Register1]" & _
        "SET [Stage 1] = " & Format(Nz(DTPicker1.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 2] = " & Format(Nz(DTPicker2.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 3] = " & Format(Nz(DTPicker3.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 4] = " & Format(Nz(DTPicker4.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 5] = " & Format(Nz(DTPicker5.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 6] = " & Format(Nz(DTPicker6.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Stage 7] = " & Format(Nz(DTPicker2.Value, "1/1/2000"), "\#mm\/dd\/yyyy\#") & " " & _
        ",[Supplier] = """ & Vendor & """ " & _
        ",[Vendor Code] = " & vendorcde & " " & _
        ",[Supply] = """ & domex & """ " & _
        ",[Engine Project] = """ & project & """ " & _
        ",[Root Cause] = """ & Root & """ " & _
        ",[Ownership] = """ & Rooto & """ " & _
        ",[Failure Mode] = """ & failm & """ " & _
        ",[Generic DFI] = """ & GENDFI & """ " & _
        ",[Refers to DFI] = """ & rfrDFI & """ " & _
        ",[Generic Comments] = """ & gencom & """,[CFBU] = """ & CFBU & """,[Customer] = " & Customer & " " & _
        ",[Region] = """ & Region & """ " & _
        ",[Comments] = """ & COMM & """, [Reasons for Deletion] = """ & RFD & """, [Customer Interface Leader] = """ & CIL & """ " & _
        "WHERE ([DFI No] = """ & DFII & """)"

Thanks again

Dan

 

[Cronk]

You don't get an error in trying to execute the SQL?

The code you posted

SQL = "Update [Register1]" & _
        "SET [Stage .....

is going to generate the SQL string

Update [Register1]SET [Stage 1] = .....

ie no space before the SET.

Paul proffered sage advice with using Debug.Print

 

[danmack]

Hi, no it seems to be working fine. Pauls advice has helped loads, i was aware of Debug print but have underused it! The tip about putting SQL into a query builders SQL view has been invaluable.

 

[pr2-eugin]

Glad to be of help ! Good Luck !