Help Required (1 Viewer)

[vicsalt]

I have a table being populated via various forms etc, I am capturing dates and times via VB depending on various states.
My issue, 2 fields is being populated in the wrong formats as in mm,dd,yyyy (American) when I need it in English Format dd,mm,yyyy. My colleague worked on the code , he has looked into the issue but as yet no solution . We have tried asdding the Format to the coding but this hasn't worked. All properties in the tables are set to: Sort Date & General Date, All PCs are on the correct reginal settings (UK)

VB Code
Table = WorksRequest_tbl
Cells giving wrong Format are DateClosed & CloseDateTime.

Private Sub LogOff_btn_Click()
EngEndTimeDate = Now()
'EngEndTimeDate = Format(Now(), "dd/mm/yyyy")
Live = False
EngDateEnd = Date

Dim WOIDAccept As Integer
Dim strSQL As String

Dim nowtime As Date
'nowtime = Format(Now(), "dd/mm/yyyy")
nowtime = Now()
WOIDAccept = Me.WOID 'name of the control with the WOID
strSQL = "UPDATE WorkRequests_tbl SET WorkRequests_tbl.WIP = False WHERE WOID = " & WOIDAccept & ";"

strSQL = "UPDATE WorkRequests_tbl SET "

If Me.Status_combo = "Closed" Then
strSQL = strSQL & "WorkRequests_tbl.CloseDateTime = #" & nowtime & "#, "
strSQL = strSQL & "DateClosed = #" & Format(nowtime, "dd/mm/yyyy") & "#, "
strSQL = strSQL & "Status = 'CLOSED', "
End If

strSQL = strSQL & "WorkRequests_tbl.WIP = False "
strSQL = strSQL & "WHERE WOID = " & WOIDAccept & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.Close acForm, "EngJobList_frm"
DoCmd.Close acForm, "EngUpdate_frm"
DoCmd.Close acQuery, "engjoblivelistsimple_qry"

End Sub


I appreciate any advice , not I'm not very clued on VB

 

[theDBguy]

Hi. No matter what your Regional Settings is, when you use code, you need to use dates in either the US or ISO format.

Format(DateVariable, "yyyy-mm-dd")

 

[vicsalt]

Hi. No matter what your Regional Settings is, when you use code, you need to use dates in either the US or ISO format.

Format(DateVariable, "yyyy-mm-dd")

So, I use the 2 dates to establish time differences, are u suggesting if I change the format to US all dates within the table will be US format ?

 

[Pat Hartman]

Don't confuse how a date is formatted with how a date is stored. SQL Server, Jet/ACE, and Excel all use similar techniques but with a different origin date. All three use a double precision data type to store a date. The integer portion is the number of days before (negative) or after (positive) the 0 date. The decimal portion is the fraction of the day. In the case of Access, the 0 date is defined as Dec 30, 1899. Therefore Dec 31, 1899 is 1 and Dec 29, 1899 is -1. 6 AM is .25, Noon is .5 and 6 PM is .75 and all the rest of the times fit in the gaps. So, 6 AM on Jan 2, 1900 is 3.25. As you might imagine, all math is done using the double precision number. The result is then converted for human consumption.

Now we get to the issue. SQL Server ASSUMES an ambiguous string date is in US date format so 1/12/2024 is Jan 12th, not Dec 1st.

Your code and queries should NEVER convert a date to a string except for display. However, when you are creating SQL strings in VBA, you are perforce working with a string date. Therefore, you need to format the date field within the string into US date format to ensure that the query engine understands the ambiguous value and stores it correctly. Once that is done, your date will always display what you expect it to.

Your code needs to be:

strSQL = strSQL & "WorkRequests_tbl.CloseDateTime = #" & Format(Now(), "mm\/dd\/yyyy") & "#, "
OR
strSQL = strSQL & "WorkRequests_tbl.CloseDateTime = Now(), "
OR
strSQL = strSQL & "WorkRequests_tbl.CloseDateTime = Forms!yourform!yourdatecontrol, "


Notice the difference. In the first case, you are converting Now() to a string but in the second case, you haven't converted anything. The query engine is working directly with the Now() function which returns a date data type, NOT a string. Now() only returns a string when you are printing it. In the third case the query is referencing a control on a form. This is the "iffy" situation. This works IF the control is either bound to a date datatype field or the unbound control has a date format so that Access knows you want this control to hold a date.

If you prefer, you can use the unambiguous yyyy/mm/dd field order whenever you are working with a string. Ultimately, this solution will probably be less confusing to you.

And finally, you seem to have two fields that you are setting to the same value. Remove one of the fields. This is probably an artifact of the confusion regarding how to handle non-US format date STRINGS.

Remember, only when the date is a STRING is there ever a problem. Hence the admonition to never convert your dates to a string in your queries. And only convert the date to a string when you specifically want it to be a string. When the query is created as a string embedded in VBA as you are doing, then you are stuck. Most of the time you will be working with the date as a string so you need to use US date format or the unambiguous yyyy/mm/dd. Personally, I almost never use embedded SQL. I use querydefs, but that's a different discussion

 

[CJ_London]

just use

strSQL = strSQL & "DateClosed = now(), "

which stores the date and time (and you can format a control to show either or both)

or if you just want the date

strSQL = strSQL & "DateClosed = date(), "

 

[theDBguy]

So, I use the 2 dates to establish time differences, are u suggesting if I change the format to US all dates within the table will be US format ?

No, I am not suggesting that. All I am saying is when using dates in code as literal strings, they have to be in either US or ISO format. I believe the others already explained the reason for that.

 

[vicsalt]

just use

strSQL = strSQL & "DateClosed = now(), "

which stores the date and time (and you can format a control to show either or both)

or if you just want the date

strSQL = strSQL & "DateClosed = date(), "

Thankyou this has sorted my Issue Happy Bunny

 

[vicsalt]

Thankyou all for your support, suggestion's, now sorted

 

[Pat Hartman]

I hope you read #4 because that post explained the issue and gave you the same answer you thanked CJ for. If you don't understand what caused the problem, you will constantly be trying to work around it.