Filtering on date problem (1 Viewer)

[FISHiEE]

Hi,

I have what to me seems to be a very wierd occurance in my work database.

I'm trying to fimler a report to display data over a certain date range. the user enters the start and end dates on a form and then code runs a rport and filters it based on the dates (and other fields) on a form.

The problem is I want the user to enter the date in the normal format dd/mm/yy which does not work and returns zero records every time.

I have tried writing the same filter parameters in the query designer (exactly the same text as produced by the form) and it works perfectly fine. However if I switch to SQL view the sql for the date filter has the format as mm/dd/yyyy.

If I sent the date in that format via the form it filters perfectly however I want the user to enter the date in the normal dd/mm/yy format. Is that normal or have I inadvertently set something to do this on my form/query?

Either way I'd appreciate it if someone can point me to a solution to this problem as it's driving me mad!

 

[FISHiEE]

Ah... I'd searched this forum for a while with no joy but as soon as I post this question it suggests similar questions of which the first had the answer. Dunno what I was doing wrong when trying to find it!

The solution is to format the date as follows:

Format([MyDatefield], "mm/dd/yyyy")

Seems this is normal for MS Access. I presumed it would take some notice of how you have your system date format set, but apparently not!

 

[Brianwarnock]

Ah... I'd searched this forum for a while with no joy but as soon as I post this question it suggests similar questions of which the first had the answer. Dunno what I was doing wrong when trying to find it!

The solution is to format the date as follows:

Format([MyDatefield], "mm/dd/yyyy")

Seems this is normal for MS Access. I presumed it would take some notice of how you have your system date format set, but apparently not!

Its VBA that has the hangup about non American dates, I think that there is a thread about it either in sample databases or code, but I couldn't find it before you came back.

Brian