Access Date Format Input

Hi, I've searched the forum for an answer to my problem. I found an archived thread with the same problem but unfortunatly it's solution isn't very clear. The archived thread is here :

http://www.thescripts.com/forum/thread204990.html

I have a query with the fields [DateWorked] containing short dates, [StaffPIN] containing long ints, and a calculated field:

Hours: WorkedHours([DateWorked], [StaffPIN])

The function WorkedHours() is a user defined function. It returns the number of hours a staff member worked on a day, or an abbreviation of why they didn't work. It works fine in the intermediate window of the VB editor when I type:

?WorkedHours(#01/11/07#,12177)

And it works if I type (the point of giving two examples will be explained later):

?WorkedHours(#21/11/07#,12177)

The formats of the above dates is dd/mm/yy, the same as my (UK) system setting (I have checked).

In the query, however, it only works on dates where the day value is larger than twelve. When the day is less than twelve, the query seems to pass the date to WorkedHours() in the format yy/dd/mm. WorkedHours is expecting it in the dd/mm/yy format.

Hope that made sense!
How can I solve this?

Hi

In the user defined function you will need to format the date as American date, ie.
Format(YourDateVariable,"mm/dd/yy")

Without seeing the code it is difficult to be more explicit.

VBA (and VB) only recognises/uses American dates, whereas the query designer will look at the local computer settings and adjusts accordingly.
Try typing a date in the criteria field for a date in query design grid, and then look at the SQL view !!

BTW, if you are returning many records, I find queries run faster if you can do the calculation in the query rather that a user defined function. This is normaly possible if the DB is stucture properly!!??