Help Required

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

 

[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