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

[pietlinden]

format them in mm-dd-yyyy format.

[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
<pi********@hot mail.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

format them in mm-dd-yyyy format.

[Randy Harris]

* 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

Are the controls bound? If so, are the fields they're bound to
Date/Time fields or Text fields?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[John]

Controls are not bound, they are just for user to enter date criteria for
query to pick up records for.

Thanks

Regards

"Randy Harris" <pl****@send.no .spamwrote in message
news:EV******** ************@ne wssvr29.news.pr odigy.net...

>* 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

Are the controls bound? If so, are the fields they're bound to Date/Time
fields or Text fields?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Lyle Fairfield]

John wrote:

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

If I had to deal with this I would ... probably ... maybe ... create a
public function that translated 12/07/2006 to #07/12/2006#.

Here is an example (there are many ways of doing this).

Public Function SQLStringFromDD MMYYYY(ByVal DDMMYYYY As String) As
String
Dim aOldParts() As String
Dim aNewParts(0 To 2) As String
aOldParts = Split(DDMMYYYY, "/")
aNewParts(0) = aOldParts(1)
aNewParts(1) = aOldParts(0)
aNewParts(2) = aOldParts(2)
SQLStringFromDD MMYYYY = _
"#" & Join(aNewParts, "/") & "#"
End Function

Then I would use the function to create my SQL as:

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

If the Batch Invoices Form had a module I would modify this slightly to
"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDD MMYYYY(Form_Bat ch_Invoices.Fro mDate) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDD MMYYYY(Form_Bat ch_Invoices.ToD ate)

or if the code is run in the form module to
"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDD MMYYYY(Me.FromD ate) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDD MMYYYY(Me.ToDat e)

or

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDD MMYYYY(FromDate ) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDD MMYYYY(ToDate)

This all assumes you are using Access >= 2000 and that FromDate and
ToDate are strings in the format dd/mm/yyyy

Actually I wouldn't do this at all. I would provide the users with some
sort of calendar or drop down to choose the date ... but that is
another story.

As I just typed the SQL there may be syntax errors of course. The
function !!!!should!!!! be OK.

[Larry Linson]

"John" wrote

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.

In the same code where you build the Query, use the various Date functions,
e.g., DatePart, to modify the dates entered by the users to U.S. date
format. Frustrating, perhaps, but that's the way Jet SQL works.

Larry Linson
Microsoft Access MVP

[Randy Harris]

* John:

Controls are not bound, they are just for user to enter date criteria for
query to pick up records for.

Thanks

Regards

"Randy Harris" <pl****@send.no .spamwrote in message
news:EV******** ************@ne wssvr29.news.pr odigy.net...

>* 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

Are the controls bound? If so, are the fields they're bound to Date/Time
fields or Text fields?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

In that case the data is strictly text. It might look like a date but
to Access it's not. The Format function is not going to be of any help
to you.

You'll need to write your own code to rearrange the text into something
Access will correctly interpret as a date.

Something along the lines of this:

Public Function FixDate(strDate As String)
Dim D1 As Integer, D2 As Integer
D1 = InStr(strDate, "/")
D2 = InStr(D1 + 1, strDate, "/")
FixDate = CDate(Mid(strDa te, D1 + 1, D2 - (D1 + 1)) & "/" _
& Left(strDate, D1 - 1) & "/" _
& Mid(strDate, D2 + 1))
End Function
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Svetlana]

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

[RoyVidar]

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

When "speaking" with the Jet engine through dynamic SQL, Jet needs
dates
in an unambiguous format, else it wont be able to understand which date
is meant. That means ISO 8601, US format or perhaps other formats, but
not UK format. Here are samples of those.

format$(yourdat e, "yyyy-mm-dd")
format$(yourdat e, "mm\/dd\/yyyy")

See for instance http://allenbrowne.com/ser-36.html for more info.

--
Roy-Vidar