By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,809 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,809 IT Pros & Developers. It's quick & easy.

Dates in where clause

P: n/a
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
Jul 12 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
format them in mm-dd-yyyy format.

Jul 13 '06 #2

P: n/a
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********@hotmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
format them in mm-dd-yyyy format.

Jul 13 '06 #3

P: n/a
* 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.
Jul 13 '06 #4

P: n/a
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********************@newssvr29.news.prodigy .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.

Jul 13 '06 #5

P: n/a

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 SQLStringFromDDMMYYYY(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)
SQLStringFromDDMMYYYY = _
"#" & Join(aNewParts, "/") & "#"
End Function

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

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDDMMYYYY([Forms]![Batch Invoices]![FromDate]) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDDMMYYYY([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])>=" _
& SQLStringFromDDMMYYYY(Form_Batch_Invoices.FromDate ) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDDMMYYYY(Form_Batch_Invoices.ToDate)

or if the code is run in the form module to
"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDDMMYYYY(Me.FromDate) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDDMMYYYY(Me.ToDate)

or

"SELECT * " & _
"FROM Orders " & _
" WHERE Orders.[Delivery Date])>=" _
& SQLStringFromDDMMYYYY(FromDate) _
& " Orders.[Delivery Date]<=" _
SQLStringFromDDMMYYYY(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.

Jul 13 '06 #6

P: n/a
"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
Jul 13 '06 #7

P: n/a
* 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********************@newssvr29.news.prodigy .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(strDate, 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.
Jul 13 '06 #8

P: n/a
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") & "# "

Jul 13 '06 #9

P: n/a
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$(yourdate, "yyyy-mm-dd")
format$(yourdate, "mm\/dd\/yyyy")

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

--
Roy-Vidar
Jul 13 '06 #10

P: n/a
Hi John,

Dates are nasty things. In tables and forms they have the local format,
in a sql-expressions it must be in the american notation.
I solved the problem as follows:

Instead of writing
"SELECT * FROM ORDERS WHERE Delivery_date " & From_date ....
i use
"SELECT * FROM ORDERS WHERE Delivery_date " & As_date(From_date)
....
As_date is a simple function:

Function As_date(cur_date as Variant) as Variant
If (IsNull(cur_date) ) Then Exit Function

As_date = "#" & Format(cur_date,"mm-dd-yyyy") & "#"
End Function

Success, HBInc.
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
Jul 13 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.