help witj date

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
alvin said:
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
Click to expand...


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.
 
Back
Top