Dates in where clause

[John]

Hi

I have a form with tow fields for dates in dd/mm/yyyy format. I am trying to
use the fields in a query's where clause as below;

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "

My problem is that query doe snot read the dates correctly and rather takes
them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was entered,
query brings records for 07/01/2006 (mm/dd/yyyy).

What is the ideal way to deal with the dates in this case so the dates are
taken as dd/mm/yyyy by the query?

Thanks

Regards

 

[Pieter Wijnen]

use US format or military format
ie
mm/dd/yyyy or yyyy-mm-ddd

Pieter

 

[Pieter Wijnen]

use US format or military format
ie
mm/dd/yyyy or yyyy-mm-ddd

Pieter

Hi

I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
to use the fields in a query's where clause as below;

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #" &
Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "

My problem is that query doe snot read the dates correctly and rather
takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
entered, query brings records for 07/01/2006 (mm/dd/yyyy).

What is the ideal way to deal with the dates in this case so the dates are
taken as dd/mm/yyyy by the query?

Thanks

Regards

--

 

[John]

Where? How should I modify the where clause? I can't expect the user to
enter date in non-dd/mm/yyyy format as they are all used to it due to the
locale.

Thanks

Regards

"Pieter Wijnen"

use US format or military format
ie
mm/dd/yyyy or yyyy-mm-ddd

Pieter

Hi

I have a form with tow fields for dates in dd/mm/yyyy format. I am trying
to use the fields in a query's where clause as below;

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #"
& Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "

My problem is that query doe snot read the dates correctly and rather
takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
entered, query brings records for 07/01/2006 (mm/dd/yyyy).

What is the ideal way to deal with the dates in this case so the dates
are taken as dd/mm/yyyy by the query?

Thanks

Regards

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4197 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Douglas J. Steele]

You're using the Format function to explicitly put the dates in dd/mm/yyyy
format. Change that to mm/dd/yyyy or yyyy-mm-dd.

You might find it usefule to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html, or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Where? How should I modify the where clause? I can't expect the user to
enter date in non-dd/mm/yyyy format as they are all used to it due to the
locale.

Thanks

Regards

"Pieter Wijnen"

use US format or military format
ie
mm/dd/yyyy or yyyy-mm-ddd

Pieter

Hi

I have a form with tow fields for dates in dd/mm/yyyy format. I am
trying to use the fields in a query's where clause as below;

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>= #" & Format([Forms]![Batch
Invoices]![FromDate], "dd/mm/yyyy") & "# And Orders.[Delivery Date]<= #"
& Format([Forms]![Batch Invoices]![ToDate], "dd/mm/yyyy") & "# "

My problem is that query doe snot read the dates correctly and rather
takes them as in mm/dd/yyyy format i.e. if 01/07/2006 (dd/mm/yyyy) was
entered, query brings records for 07/01/2006 (mm/dd/yyyy).

What is the ideal way to deal with the dates in this case so the dates
are taken as dd/mm/yyyy by the query?

Thanks

Regards

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4197 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Michel Walsh]

Hi,


Alternatively, if the string is used as record source or as row source, you
can bring back the FORMS!FormName!ControlName in the string:

" SELECT * FROM Orders " &
" WHERE Orders.[Delivery Date])>= [Forms]![Batch Invoices]![FromDate] " &
" And Orders.[Delivery Date]<= [Forms]![Batch Invoices]![ToDate] "


no delimiter, no format.

Hoping it may help,
Vanderghast, Access MVP