Date format delimiters? / vs .

[Alp Bekisoglu]

Hi Experts,

I am facing sytax errors due to different types of date format delimitors.
Such as / (slash) versus . (dot).
I am using the following code excerpt in my query:
....#" & Format(fieldname,"dd/mm/yyyy") & "#.....

How should I change the Format statement so that it uses the users' date
delimiter?

Thanks in advance.

Alp

 

[Douglas J. Steele]

Using / in the Format statement should pick up the user's delimiter. If you
want to force it to be /, you need to use Format(fieldname,"dd\/mm\/yyyy")

However, be aware that regardless of what your Short Date format has been
set to through Regional Settings, you cannot use dd/mm/yyyy in SQL queries.
Access will ALWAYS treat nn/nn/nnnn as mm/dd/yyyy unless that's an invalid
date (i.e.: if the first two digits are 13 or higher). That means you should
be using:

....#" & Format(fieldname,"mm\/dd\/yyyy") & "#.....

or, my preference,

...." & Format(fieldname,"\#mm\/dd\/yyyy\#") & "....

 

[Allen Browne]

In the example you posted, the Format() function should interpet the date
seperators with whatever the user's character is, i.e. it will replace the
slashes with dots if the user's settings is for dots.

But your sample also encloses the dates in the # delimiter. If you are doing
this in VBA code, or in any string which is passed to JET - such as a query
statement, or an argument for DLookup() - you need to use the mdy format,
with slashes (not dots.) Therefore you would use:
" ...WHERE MyDate = " & Format([fieldname], "\#mm\/dd\/yyyy\#")

The backslashes in that statement instruct Format() to treat the next
character as a literal, so it does not use the dots from the user's
settings.

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Alp Bekisoglu]

Hi Allen,

Thanks for the very quick advice. My code behind the form (button code) is:
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Me.sorguborc = DSum("borc", "zqry_har_sch_uni", "[firma]=" &
[Forms]![fr_co_main_muh]![m_co_main_id] & " And [fuar]=" &
[Forms]![fr_co_main_muh]![fair_id] & " And [katilim]=" &
[Forms]![fr_co_main_muh]![kat_id] & " And [tarih]<=#" &
Format(Forms!fr_zqry_har_sch_uni_2!Text25, "mm/dd/yyyy") & "#")
Me.sorgualacak = DSum("alacak", "zqry_har_sch_uni", "[firma]=" &
[Forms]![fr_co_main_muh]![m_co_main_id] & " And [fuar]=" &
[Forms]![fr_co_main_muh]![fair_id] & " And [katilim]=" &
[Forms]![fr_co_main_muh]![kat_id] & " And [tarih]<=#" &
Format(Forms!fr_zqry_har_sch_uni_2!Text25, "mm/dd/yyyy") & "#")
Me.sorgutarih = Me.Text25 & " itibariyle"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

So as you advise, I should change to m/d/y format. But the issue is; this
works properly without a hitch on my nb (WinXP) and one of the user PC's
which the date formats on both are set as dd/mm/yyyy in Windows. But the
rest are having the error message! ?? And all data is recorded based on
dd/mm/yyy basis so if in the Format() I use fieldname,"mm/dd/yyy" wouldn't
it interpret the day and month wrongly?

Thanks,

Alp

In the example you posted, the Format() function should interpet the date
seperators with whatever the user's character is, i.e. it will replace the
slashes with dots if the user's settings is for dots.

But your sample also encloses the dates in the # delimiter. If you are
doing this in VBA code, or in any string which is passed to JET - such as
a query statement, or an argument for DLookup() - you need to use the mdy
format, with slashes (not dots.) Therefore you would use:
" ...WHERE MyDate = " & Format([fieldname], "\#mm\/dd\/yyyy\#")

The backslashes in that statement instruct Format() to treat the next
character as a literal, so it does not use the dots from the user's
settings.

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am facing sytax errors due to different types of date format
delimitors. Such as / (slash) versus . (dot).
I am using the following code excerpt in my query:
...#" & Format(fieldname,"dd/mm/yyyy") & "#.....

How should I change the Format statement so that it uses the users' date
delimiter?

 

[Bob Hairgrove]

mm/dd/yyyy

Try this format string instead:

mm\/dd\/yyyy

The slash is interpreted by the format function to be whatever the
system locale's date separator is. If it is something other than "/"
there will be problems. The backslash forces the format function to
use the exact character given after it in the expression.

 

[Alp Bekisoglu]

Thanks for your help Allen, Doug and Bob.

When I forced the date seperator (..\/..) that did the trick. Thanks again
for the guidance.

Alp