Trouble with date format

[Guest]

Hello,

I have a query that returns a date. When I see the output of the query, the
date is on the form dd.mm.yyyy , which is the standard Norwegian format, and
the format that is goven for short date in my regional settings in Windows.

Then I try looking up the record of a specific date, by entering 25.11.2006
in the date's criteria field, and sure enough, it returns only the records
that got this specific date in the date field. Access adds # in front and
behind the date.

However, when I try using this query as the row source for a list box in a
form I have, it all goes wrong. The relevant part of the SQL looks like this:

me.mylist.rowsource = ............ "WHERE myquery.date = #" me.DateInForm
"#;"

It enters the correct date in the rowsource, but I get a syntax error
message none the less.

I went back to the query, and entered 25.11.06 in the date criterea, and it
still worked. Then I looked at the query in SQL view, and to my surprise it
had re-written the WHERE clause's date-format to 11/25/2006. I have no idea
where it gets this format from, since everywhere I look in my application,
the dates are in the norwegian format.

I looked through the rest of my queries, and it seems like the same problem
occurs in every single one.

Can anyone give me an idea of what is going on here, and whay I can do to
remedy the problem? I spent 6 hours before I finally spotted the root of the
problem, so now I am really at my wit's end.

All help would be gratly appreaciated.

Baard

 

[Rick Brandt]

Hello,

I have a query that returns a date. When I see the output of the
query, the date is on the form dd.mm.yyyy , which is the standard
Norwegian format, and the format that is goven for short date in my
regional settings in Windows.

Then I try looking up the record of a specific date, by entering
25.11.2006 in the date's criteria field, and sure enough, it returns
only the records that got this specific date in the date field.
Access adds # in front and behind the date.

However, when I try using this query as the row source for a list box
in a form I have, it all goes wrong. The relevant part of the SQL
looks like this:

me.mylist.rowsource = ............ "WHERE myquery.date = #"
me.DateInForm "#;"

It enters the correct date in the rowsource, but I get a syntax error
message none the less.

I went back to the query, and entered 25.11.06 in the date criterea,
and it still worked. Then I looked at the query in SQL view, and to
my surprise it had re-written the WHERE clause's date-format to
11/25/2006. I have no idea where it gets this format from, since
everywhere I look in my application, the dates are in the norwegian
format.

I looked through the rest of my queries, and it seems like the same
problem occurs in every single one.

Can anyone give me an idea of what is going on here, and whay I can
do to remedy the problem? I spent 6 hours before I finally spotted
the root of the problem, so now I am really at my wit's end.

All help would be gratly appreaciated.

Baard

Date literals in Access queries MUST be in US format or an unambiguous format
like ISO (yyyy-mm-dd) or a format that uses alpha characters for the month. If
it used regional settings then the same query would produce different results on
different PCs.

 

[Guest]

ok, fair enough, but how do I get my code to work with the date I have in the
text box?

I just tried using format(me.DateInForm, "mm/dd/yyy") in the code, but that
converted the date to 11.25.2006, so that didn't work either

thx for the reply

Baard

 

[Douglas J. Steele]

In the Format statement, using / with a date tells VBA to use whatever was
specified as the date separator in Regional Settings. To force the date
separator to be /, you need to escape it, using \. In other words,

Format(Me.DateInForm, "mm\/dd\/yyy")

 

[Guest]

Thank you very much, i got it working now

thats 6 hous of my life I won't get back....

Baard