How do I make sure I'm working with the correct dateformat. I want to use
the dmy format all over the application (forms control, queries, tables
ect.) but I find that sometimes it seems to be mdy. (In sql I have the
dateformat command per connection, and I think I need someting like that...)

Thanks

G'Day Chedva,
When you use d/m/y format as your 'standard' (which I
also do), there are some things to watch for:
SQL is a STANDARD language that has little to do
with Microsoft. That standard REQUIRES dates to
be expressed in m/d/y format.
When you design a Query, Access 'tries' to deal with
m/d/y and d/m/y formats but often gets it wrong - the
way to cope with this is for YOU to use d-mmm-yyyy
format in all criteria and expressions (just use it -
Access will cope).
Encourage users to adopt the same format in response
to INPUTBOX statements - reject formats containing
'/', prompt with 'd-mmm-yyyy' AND insert a default
date in 'd-mmm-yyyy' format.
On Forms, modify the label for Date Fields to include
'd/m/y' as a reminder for users - OR confine them to a
'd-mmm-yyyy' input mask.
On Reports, of course, do as you please.
--
Regards,
Pat Garard
Australia

Joe Foster wrote a simple set of Contructs that will fix the date format:

http://www.mvps.org/access/datetime/date0005.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Chedva" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do I make sure I'm working with the correct dateformat. I want to use
> the dmy format all over the application (forms control, queries, tables
> ect.) but I find that sometimes it seems to be mdy. (In sql I have the
> dateformat command per connection, and I think I need someting like
that...)
>
> Thanks
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 1/30/2004

"Pat Garard" <apgarardATbigpondPERIODnetPERIODau> wrote in
news:(E-Mail Removed):

> SQL is a STANDARD language that has little to do
> with Microsoft. That standard REQUIRES dates to
> be expressed in m/d/y format.
>

Just to be precise: it's not a SQL standard, it's a MS/ Jet requirement.
And for the internationally-minded, the ISO Standard is explicitly
supported by Jet: yyyy-mm-dd

As a general rule, you should always convert user input to real dates and
then format them explicitly:

dtWhen = CVDate(txtUserTextBox)

strSQL = ".... " & Format$(dtWhen, "\#yyyy\-mm\-dd\#")


B Wishes


Tim F

"Tim Ferguson" <(E-Mail Removed)> wrote in message
news:Xns9487BCE31E606garbleme4455656@207.46.248.16...

<snip>
> dtWhen = CVDate(txtUserTextBox)
<snip>


Tim,

You evidentially started with an _early_ version of Access and Microsoft
slipped one by you.

The following is from Access 2000 help. To see it, search for CVDate and
select "Type Conversion Functions." (Next to last paragraph)

A CVDate function is also provided for compatibility with previous versions
of Visual Basic. The syntax of the CVDate function is identical to the CDate
function, however, CVDate returns a Variant whose subtype is Date instead of
an actual Date type. Since there is now an intrinsic Date type, there is no
further need for CVDate. The same effect can be achieved by converting an
expression to a Date, and then assigning it to a Variant. This technique is
consistent with the conversion of all other intrinsic types to their
equivalent Variant subtypes.


Harvey Thompson
Bloomfield, Connecticut USA

"Harvey Thompson" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> <snip>
>> dtWhen = CVDate(txtUserTextBox)
> <snip>
>
> You evidentially started with an _early_ version of Access and Microsoft
> slipped one by you.

v 1.1 actually. Yes CDate works too: the point was that simply chucking a
piece of user input into a SQL query will rapidly lead to either a dead
database or lots of error messages.

Tim F