~~ US / UK dates ~~

[Geordie2008]

Please help!

I have a query which is taking 2 date values from a form and filters between these tow dates.

Txt_FromDate
Txt_ToDate

I think its using American dates however....

when I filter between: 01/01/2008 and 02/01/2008 it returns 01/05/2008?!?!?!

How can I make all my dates DD/MM/YYY

Thanks,
M

 

[neileg]

If you are entering this as SQL or building string SQL, you'll have a problem with using UK dates. If you use the query grid, it usually works ok with uk dates. I usually use unambiguous formats like 12-Jul-2007 and this avoids the issue.

 

[Geordie2008]

Im using the following criteria on a query:

Between [Forms]![frmSwitchBoard]![Txt_FromDate] And [Forms]![frmSwitchBoard]![Txt_ToDate]

and I have set all of the dates to "Short Date"

 

[Geordie2008]

Is there a way to set the entire database to fate format:

DD/MM/YYYY?

 

[RuralGuy]

Here's the link I usually give posters with this issue.

 

[Mike375]

When I install MS Office there are the questions about locality.

If I enter 3/29/08 in a date field it will change itself to 29/3/2008

I have never had any problems but I have never written a query in SQL view, always the grid.

 

[Geordie2008]

Thanks guys,

I'll take a look at the web-site and see if I can harass Access into doing what I would like it to (!!)

Mandy

 

[Rich]

What are your regional settings in Windows?

 

[EMP]

when I filter between: 01/01/2008 and 02/01/2008 it returns 01/05/2008?!?!?!

Another possibility. It sounds like Access is treating the dates as text strings.

What's the data type of the field in the table? Text or Date/Time?

^

 

[neileg]

The date delimiter in Access is #. This tells Access that the value is a date, not text.

 

[EMP]

Im using the following criteria on a query:

Between [Forms]![frmSwitchBoard]![Txt_FromDate] And [Forms]![frmSwitchBoard]![Txt_ToDate]

Here two text boxes on the form are used for parameter input.
No # delimiter is used.

^

 

[Geordie2008]

I thought I had set all of the fields to be dates... Im going to re-check.

I have used input masks of "short-date".....

Its more annoying as its not being consistant.... if it can return results as a UK date, it does, if it cant, then it looks for US dates.....

I think Im going mad....! That cant possibly be possible can it?!?!?!

M.

 

[neileg]

Here two text boxes on the form are used for parameter input.
No # delimiter is used.
^

Of course you're correct. I didn't read the postings carefully enough!

 

[Geordie2008]

The following code is from the Allen Browne, But I dont understand where I would need to put this.....

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

 

[Geordie2008]

Also, the type of data in the field is date (not date + time)

Regards
Mandy

 

[RuralGuy]

The following code is from the Allen Browne, But I dont understand where I would need to put this.....

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

With the SQLDate function in a standard module try:
Between SQLDate([Forms]![frmSwitchBoard]![Txt_FromDate]) And SQLDate([Forms]![frmSwitchBoard]![Txt_ToDate])

 

[Geordie2008]

Thanks for this RuralGuy.

I actually cheated and just changed everything to be a "medium date" e.g. 01-Mar-08 and so far I haven't had anymore problems.

If I get any I'll give your code a whirl however!

Thanks again for looking at this for me,
Regards
Mandy

 

[RuralGuy]

Glad to pitch in. Have a good day.

 

[Rich]

You didn't answer my question

 

[gemma-the-husky]

you get this sort of behaviour if you are using sql statements

wrapping a date in format(thedate,"long date") ALWAYS seems to resolve this without ambiguity - except for one site, where the regional format kept getting changed to "Caribbean", which still caused problems!