Date Problems...

[WhizzkidWallace]

Hi,

Has anyone seen this one? I have a form on it with a date field, and want to use that date field as a criteria within a 'where' statement for a report. Problem is that on the form, if I extract the date to a variable, it comes off in a dd/mm/yy format, BUT when I want to include that variable in my 'where' clause for my report, it needs the date in the format mm/dd/yy !!!

At the moment I have got round it by extracting the Day, Month and Year from the Form date, and stringing them together in the right order for the 'where' clause (with the necessary #'s around it), but I would like to know why this happens....

Thanks

 

[neileg]

SQL always needs the date in American. Sometimes you can avoid the problem by using a medium date, 11 Jan 2005 for example. However, when you actually enter data in a form, it always seem to revert to the short date setting in your Windows Control Panel.

 

[Waabs]

Here's a piece of code I use for just this problem:

Dim daypart As Integer
Dim monthpart As Integer
Dim yearpart As Integer

'breaking apart the dates
daypart = DatePart("d", theDate)
monthpart = DatePart("m", theDate)
yearpart = DatePart("yyyy", theDate)

'doing a check for dates like 24/03/2002
If monthpart > daypart Then
theDate = monthpart & "/" & daypart & "/" & yearpart
End If

'doing a check for dates like 11/03/2002
If (daypart > monthpart) And daypart <= 12 Then
theDate = monthpart & "/" & daypart & "/" & yearpart
End If

DateSwap = theDate

 

[ghudson]

This should fix either situation...

"#" & Format([YourDate],"mm/dd/yyyy") & "#"