Working with dates

[xolo]

When I checked in my "InvDate" field in the "Inv" table, Access stored it as 39923 (serial date) which is supposed to be "April 29th, 2009". So I wonder why it returns 0 (December 30th, 1899) when I call that function. Maybe there is something wrong with my codes?

I still want to use "dd/mm/yy" format and I want my program to recognize "20/04/09" as "April 20th 2009".

Now I use this trick:
1. First, I make a query ("QInvDate") that contains ProductID, InvDate, and Price from Tables "Inv" and "InvSub"
2. Then I call the date with this:

=DMax("InvDate";"QInvDate";"ProductID= '" & [ProductID] & "' AND Price= " & DMax("Price";"InvSub";"ProductID= '" & [ProductID] & "'"))

This trick works but I still wonder if it is possible to use the function method.

 

[namliam]

I still want to use "dd/mm/yy" format and I want my program to recognize "20/04/09" as "April 20th 2009".

Very nice and well and good, what ever you want... However access SQL reads american format thus you HAVE TO use american format in your SQL, there is simply no other choice...
Or rely on the regional setting to maybe hopefully, if everything is setup right etc... etc..., but you dont want to do that!

Your DMax works because.... well geuss???

The DMax returns your InvDate as a ..... format, what format? Yes American format!

If you dont want to use american format because you think it is confusing, use ISO date (YYYY-MM-DD) or use the DateSerial function.

 

[xolo]

Your DMax works because.... well geuss???

The DMax returns your InvDate as a ..... format, what format? Yes American format!

No, it returns "dd/mm/yy" format, exactly like I want.

 

[namliam]

Well... thats just plain true offcourse, that is why your code is working...

Format(String, "Somethign") <<== DOES NOT WORK!