help witj date

[Guest]

Hi
I have this code:
hellig = DCount("Helligdag", "Helligdage", "Helligdag = #" & start & "#")

There shall count days from a table if date (Start) is in the table
My problem is this
I have a date in Helligdag = 06-05-2007
And if i say that start is 06-05-2007 then i got 0 records
but if i say that start 05-06-2007 then i get 1 records

I have try to run e Query on this:
SELECT Helligdage.Helligdag
FROM Helligdage
WHERE (((Helligdage.Helligdag)=#6/5/2007#));

Then i get one records this is all right but i can see that the SQL query
turn the date to 6/5/2007, and i write 05-06-2007 in the Qury field in design.

I know its because i have a dansih DB and danish Date system
But what to do?

Alvin

 

[Guest]

Hi

Unfortunately, VBA will use the US date system no matter what your locale is.

The way around this is to convert your 'Start' date to US format.

Try

convDate = "#" & Month(Start) & "/" & Day(Start) & "/" & Year(Start) & "#"
hellig = DCount("Helligdag", "Helligdage", "Helligdag = " & convDate)

Cheers.

BW

 

[Marshall Barton]

I have this code:
hellig = DCount("Helligdag", "Helligdage", "Helligdag = #" & start & "#")

There shall count days from a table if date (Start) is in the table
My problem is this
I have a date in Helligdag = 06-05-2007
And if i say that start is 06-05-2007 then i got 0 records
but if i say that start 05-06-2007 then i get 1 records

I have try to run e Query on this:
SELECT Helligdage.Helligdag
FROM Helligdage
WHERE (((Helligdage.Helligdag)=#6/5/2007#));

Then i get one records this is all right but i can see that the SQL query
turn the date to 6/5/2007, and i write 05-06-2007 in the Qury field in design.

I know its because i have a dansih DB and danish Date system

When you use the # delimiter around a literal date value, it
must be in either US format (m/d/y) or in an unambiguous
format (y-m-d). When you let Access convert a date
variable to a string, as you did in the concatenation, it
uses your windows setting, which, in this case, is d/m/y.

The way to avoid all this confusion is to explicitly format
the date:

. . . , "Helligdag = " & Format(start, "\#m\/d\/yyyy\#")

The reason for escaping the / is because it will also be
converted to your Windows setting for the date separator and
that might not be one that Access can decipher.