Question Date changes from DD/MM/YYYY to MM/DD/YYY

[shabbaranks]

Howdy all...

I have a very strange problem, when I submit a date using a date picker from a form to a subform the date changes from dd/mm/yyyy to mm/dd/yyyy I have checked the date format of the table and its set to short date.

Im too sure where or what else to check - any ideas please?

Thanks

 

[boblarson]

Is your WINDOWS REGIONAL SETTINGS set properly? That is what determines which order they are in, not Access.

 

[shabbaranks]

Just reading up, is it because Im using the below code:

Mysql = "INSERT INTO TimesheetTableTemp (Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', #"
Mysql = Mysql & sDate & "#)"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

And mysql will always use mm/dd/yyyy - is there anyway around this?

Thanks

 

[shabbaranks]

Hi Bob... Its not the regional settings, they are UK based. I think its the above mysql statement?

 

[boblarson]

The SQL query MUST use U.S. formatted dates in the criteria. It matters not how it is stored in the table (as long as it is a date field).

 

[shabbaranks]

Sorry Im a bit confused by your statement, are you saying as the field on the table is defined as short date UK it should store as dd/mm/yyyy?

Thanks

 

[gemma-the-husky]

shabba

where exactly are you seeing the strange date?

 

[shabbaranks]

Hi,

The date converts to mm/dd/yyyy when a button is pressed which runs the VBA code and adds the jpicker date to the subform table.

 

[gemma-the-husky]

yes - but where?

in a control in the subform?
or in the table?

 

[Galaxiom]

The SQL shown will read the date in whatever style it is entered in sDate and take it to be MM/DD/YYYY. That is how it will turn up in the table.

You need to explicitly convert the sDate to MM/DD/YYYY

I use this to convert and add the delimiters in the one step.

Format(sDate, "\#dd\/mm\/yyyy\#")

BTW, if sDate is a control on the form you should be properly referencing it as Me.sDate
Same with the others if they are controls.

 

[shabbaranks]

Oh sorry, again if I'm understanding this properly - its a control in the main form which applies the date to the subform. This is initiated via the vba command, and is where the date goes funny. But I think I would need to add the convert at the next stage, as there is another vba control which takes the contents of the sub form and adds them to a table, so its pointless doing it at the initial point I need to do it at the end.

Could anyone assist please?

 

[gemma-the-husky]

can we go over this again. there are a lot of potential areas to check?

you have a control in the main form showing a date? another control in a subform showing a date? are either/both/none of these controls bound? if so, what are they bouind to? what format do you have in the controls? what format do you have in the tables? which data picker are you using? what display format is the subform - single/continuous/datasheet?

what code are you using to get the date from the date-picker into the main form, and into the subform?

finally, what happens if you pick a non-ambiguous date, like 14/11/11?

the solution is in here somewhere, but as you can see, there are quite few places to check when things start to go wrong.

 

[shabbaranks]

Okie dokie... Here's how it stands:

I have a date picker in the main form which is a MSComCtl2.DTPicker.2 once a date is selected it adds the selection to a textbox(Task Date) which has a control source of Task Date (from temp table column - Task Date). The subform is Datasheet format.

The tables - temp table (Task Date) is in short date format. The code to get the data from the main form tot he subform is:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Mysql As String
Dim sLogUser As String
Dim sDate As Date

If Me.Activity = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.Activity.SetFocus
    Exit Sub
End If

If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If

If Me.LoggedInUser = "" Then
    MsgBox "You must enter activity before continuing", vbInformation
    Me.LoggedInUser.SetFocus
    Exit Sub
End If


sActivity = Me.Activity
sProject = Me.Project
sHours = Nz(Me.Hour, 0)
sDescript = Me.Description
sLogUser = Me.LoggedInUser
sDate = Me.DatePicker

Mysql = "INSERT INTO TimesheetTableTemp (Activity, Project, Hours, Description, suser, [task date]) Values "
Mysql = Mysql & "('" & sActivity & "', '" & sProject & "', " & sHours & ", '" & sDescript & "', '" & sLogUser & "', #"
Mysql = Mysql & sDate & "#)"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
ClearField
End Sub

if a date is picked out of the month range as per your example its fine and adds it correctly.

Thanks for your time on this

 

[DavidAtWork]

If you haven't already solved this try changing your sql where it reads:
#" & sDate & "#
try changing it to: ' " + Format(sDate) + " '
and if that doesn't work try
'" + Format(sDate, "mm/dd/yyyy") + "'

 

[shabbaranks]

Cheers David - I wasnt ignoring your response I had been away for a bit. This worked an absolute treat - thanks