Date problems

[Stapes]

Hi
I have on my input forms two input fields defined as Format:Short
Date.
The user sees __/__/____ when beginning to type.
I put in 01/12/2006 and 19/02/2007 (meaning 1st December 2006 and 19th
February 2007)
Then my sql looks for data matching the criteria

WHERE (((TM_Invoice.InvoiceDate) BETWEEN #" & TXT_StartDate & "# AND
#" & TXT_ToDate & "#))

which it translates as:

WHERE (((TM_Invoice.InvoiceDate) BETWEEN #12/01/2006# AND
#19/02/2007#))

Notice that it has transposed the month and the day, but only on the
first date. I guess the second would produce an illegal date
transposed in this way.

How do I get round this?

Stapes

 

[John W. Vinson]

Hi
I have on my input forms two input fields defined as Format:Short
Date.
The user sees __/__/____ when beginning to type.
I put in 01/12/2006 and 19/02/2007 (meaning 1st December 2006 and 19th
February 2007)
Then my sql looks for data matching the criteria

WHERE (((TM_Invoice.InvoiceDate) BETWEEN #" & TXT_StartDate & "# AND
#" & TXT_ToDate & "#))

which it translates as:

WHERE (((TM_Invoice.InvoiceDate) BETWEEN #12/01/2006# AND
#19/02/2007#))

Notice that it has transposed the month and the day, but only on the
first date. I guess the second would produce an illegal date
transposed in this way.

How do I get round this?

Stapes

You must always use the American mm/dd/yyyy format for literal dates.
Access was programmed in the US and the developers chose to take this
as a standard.

Try

WHERE (((TM_Invoice.InvoiceDate) >= Format(Me!TXT_StartDate,
"mm/dd/yyyy") AND (TM_Invoice.InvoiceDate) < DateAdd("d", 1,
Me!TXT_StartDate, "mm/dd/yyyy"))

The DateAdd covers the possibility that InvoiceDate might have a time
component.

John W. Vinson [MVP]