Date Format Problem

[Guest]

I've produced a Access 2003 which works fine except one user has set his date
format, via the Control Panel - Regional Options, to use '.' to delimit the
date rather than the '/' which everyone else uses.

The problems appear to occur when attempting to use filters and queries.
I’ve tried using format e.g. Format(Me!lstCourses.Column(3), "mm/dd/yyyy") to
try to change it to the required format but this doesn’t appear to work.

Can anyone please suggest a solution please? Can I also add that I would
prefer users to be able to use any format they wanted if I could.

 

[Allen Browne]

Yes, the Format() function does "interpret" the slashes like that.

To avoid the problem, precede ecah slash with backslash - to indicate the
next character is a literal, i.e.:
Format(Me!lstCourses.Column(3), "mm\/dd\/yyyy")

If you want the string to include the literal date delimiters as well, use:
Format(Me!lstCourses.Column(3), "\#mm\/dd\/yyyy\#")

 

[Douglas J. Steele]

With dates, the Format statement always replaces / with whatever is defined
as the date separator character. If you want to force it to be slashes, you
need to "escape" the slashes in the Format statement:

Format(Me!lstCourses.Column(3), "mm\/dd\/yyyy")

(in case it's not obvious, that's \, following by /, not the letter V!)

 

[Guest]

Thank you both ... works perfectly now.