Problem with date format??

G

Guest

Hi,
I have a form that "builds" an SQL statement depending on the selections on
the form. Everything works fine except for the last part of the statement
where a date criteria is set. The SQL statement, when executed, should return
records with a "QueueDate" <= 11/10/2004 but for some reason records are
returned with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yy')
AS QueueDate, tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name, tbl_Locations.str_FullName AS Origin,
tbl_Locations_1.str_FullName AS Destination, Tbl_Bookings.str_Diagnosis AS
Diagnosis, Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID WHERE (((Tbl_Bookings.str_Status)= "Waiting"
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
 
W

Wolfgang Kais

Hello Proko.

Proko said:
Hi,
I have a form that "builds" an SQL statement depending on the
selections on the form. Everything works fine except for the last
part of the statement where a date criteria is set. The SQL
statement, when executed, should return records with a
"QueueDate" <= 11/10/2004 but for some reason records are returned
with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL
statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]
+[dte_BookingTime]),'dd/mm/yy hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),
'dd/mm/yy') AS QueueDate,
tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title
& " " & tbl_Bookings.str_FirstName AS Name,
tbl_Locations.str_FullName AS Origin, tbl_Locations_1.str_FullName
AS Destination, Tbl_Bookings.str_Diagnosis AS Diagnosis,
Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID =
tbl_Locations.lng_LocationID) INNER JOIN tbl_Locations AS
tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID
WHERE (((Tbl_Bookings.str_Status)= "Waiting" AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),
'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...

It seems that you and the query speak of different dates. It seems to
me that you think that #11/10/2004# is Oktober 11th 2004, but for
access this is November 10th...
In a SQL query, dates are always formatted in US format: #mm/dd/yyyy#.
When "building" criteria, I love to use the BuildCriteria method:
stLinkCriteria = BuildCriteria("[MyField]", dbDate, CStr(me.txtDate))

It seems that the fields [dte_TransportDate], [dte_BookingDate] and
[dte_BookingTime] are of type "Date/Time", so a "formatting" should
not be necessary.
 
G

Guest

Thankyou, I'll play with it. You are correct about the way I was thinking
about dates!!

Wolfgang Kais said:
Hello Proko.

Proko said:
Hi,
I have a form that "builds" an SQL statement depending on the
selections on the form. Everything works fine except for the last
part of the statement where a date criteria is set. The SQL
statement, when executed, should return records with a
"QueueDate" <= 11/10/2004 but for some reason records are returned
with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL
statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]
+[dte_BookingTime]),'dd/mm/yy hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),
'dd/mm/yy') AS QueueDate,
tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title
& " " & tbl_Bookings.str_FirstName AS Name,
tbl_Locations.str_FullName AS Origin, tbl_Locations_1.str_FullName
AS Destination, Tbl_Bookings.str_Diagnosis AS Diagnosis,
Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID =
tbl_Locations.lng_LocationID) INNER JOIN tbl_Locations AS
tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID
WHERE (((Tbl_Bookings.str_Status)= "Waiting" AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),
'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...

It seems that you and the query speak of different dates. It seems to
me that you think that #11/10/2004# is Oktober 11th 2004, but for
access this is November 10th...
In a SQL query, dates are always formatted in US format: #mm/dd/yyyy#.
When "building" criteria, I love to use the BuildCriteria method:
stLinkCriteria = BuildCriteria("[MyField]", dbDate, CStr(me.txtDate))

It seems that the fields [dte_TransportDate], [dte_BookingDate] and
[dte_BookingTime] are of type "Date/Time", so a "formatting" should
not be necessary.
Click to expand...
 
J

John Spencer

Format turns the date into a STRING and that my friend will screw up the
comparisons. So drop the formatting. Also is !!/10/2004 supposed to
represent Nov 10, 2004 or October 11, 2004. Date literals
(#mm/dd/yyyy#) in an SQL query must be in US format of dd/mm/yyyy OR in
the unambiguous format of #yyyy/mm/dd#.



SELECT Format(IIf([lng_Priority]>2,[dte_TransportDate],
[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],
[dte_BookingDate]),'dd/mm/yy') AS QueueDate
, tbl_Bookings.lng_BookingID AS ID
, Tbl_Bookings.lng_Priority AS Priority
, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name
, tbl_Locations.str_FullName AS Origin
, tbl_Locations_1.str_FullName AS Destination
, Tbl_Bookings.str_Diagnosis AS Diagnosis
, Tbl_Bookings.str_Status AS Status
FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1
ON Tbl_Bookings.lng_ToID = tbl_Locations_1.lng_LocationID
WHERE Tbl_Bookings.str_Status= "Waiting"
AND (
(lng_Priority >2 and Dte_transPortDate <= #11/10/2004#)
OR (lng_priority <=2 and dte_BookingDate <= #11/10/2004#)
)




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
I have a form that "builds" an SQL statement depending on the selections on
the form. Everything works fine except for the last part of the statement
where a date criteria is set. The SQL statement, when executed, should return
records with a "QueueDate" <= 11/10/2004 but for some reason records are
returned with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yy')
AS QueueDate, tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name, tbl_Locations.str_FullName AS Origin,
tbl_Locations_1.str_FullName AS Destination, Tbl_Bookings.str_Diagnosis AS
Diagnosis, Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID WHERE (((Tbl_Bookings.str_Status)= "Waiting"
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...
 
M

Marshall Barton

Proko said:
I have a form that "builds" an SQL statement depending on the selections on
the form. Everything works fine except for the last part of the statement
where a date criteria is set. The SQL statement, when executed, should return
records with a "QueueDate" <= 11/10/2004 but for some reason records are
returned with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yy')
AS QueueDate, tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name, tbl_Locations.str_FullName AS Origin,
tbl_Locations_1.str_FullName AS Destination, Tbl_Bookings.str_Diagnosis AS
Diagnosis, Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID WHERE (((Tbl_Bookings.str_Status)= "Waiting"
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...


I don't think you want to use the Format function, because
it returns a string that you are comparing to a date. Since
the date is not in US or other unambiguous format, it will
not be reconverted back to a date correctly.

Assuming your dates really are DateTime type fields and that
you intend #11/10/2004# to mean Nov 10, this should be
sufficient:

IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate])<=#11/10/2004#
 
G

Guest

Thankyou all!! You are all correct. I was not aware that formating the date
turned it into a string. Many thanks. Problem now solved!!
Proko


Marshall Barton said:
Proko said:
I have a form that "builds" an SQL statement depending on the selections on
the form. Everything works fine except for the last part of the statement
where a date criteria is set. The SQL statement, when executed, should return
records with a "QueueDate" <= 11/10/2004 but for some reason records are
returned with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yy')
AS QueueDate, tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name, tbl_Locations.str_FullName AS Origin,
tbl_Locations_1.str_FullName AS Destination, Tbl_Bookings.str_Diagnosis AS
Diagnosis, Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID WHERE (((Tbl_Bookings.str_Status)= "Waiting"
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...


I don't think you want to use the Format function, because
it returns a string that you are comparing to a date. Since
the date is not in US or other unambiguous format, it will
not be reconverted back to a date correctly.

Assuming your dates really are DateTime type fields and that
you intend #11/10/2004# to mean Nov 10, this should be
sufficient:

IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate])<=#11/10/2004#
Click to expand...
 
J

J_Goddard via AccessMonster.com

Hi -


You can also express the date as #15-Oct-2007" in the SQL - no ambiguity and
easier to read.

John


Hi,
I have a form that "builds" an SQL statement depending on the selections on
the form. Everything works fine except for the last part of the statement
where a date criteria is set. The SQL statement, when executed, should return
records with a "QueueDate" <= 11/10/2004 but for some reason records are
returned with dates greater than 11/10/2004 as well. I'm stumped! Can someone
suggest where I may be going wrong. Here is a copy of my SQL statement:

SELECT
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]+[dte_BookingTime]),'dd/mm/yy
hh:nn') AS QueueTime,
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yy')
AS QueueDate, tbl_Bookings.lng_BookingID AS ID, Tbl_Bookings.lng_Priority AS
Priority, tbl_Bookings.str_Surname & ", " & tbl_Bookings.str_Title & " " &
tbl_Bookings.str_FirstName AS Name, tbl_Locations.str_FullName AS Origin,
tbl_Locations_1.str_FullName AS Destination, Tbl_Bookings.str_Diagnosis AS
Diagnosis, Tbl_Bookings.str_Status AS Status FROM (Tbl_Bookings INNER JOIN
tbl_Locations ON Tbl_Bookings.lng_fromID = tbl_Locations.lng_LocationID)
INNER JOIN tbl_Locations AS tbl_Locations_1 ON Tbl_Bookings.lng_ToID =
tbl_Locations_1.lng_LocationID WHERE (((Tbl_Bookings.str_Status)= "Waiting"
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...
 
J

J_Goddard via AccessMonster.com

Oops - typing error - it should read

You can also express the date as #15-Oct-2007# in the SQL - no ambiguity and
easier to read.

J.


J_Goddard said:
Hi -

You can also express the date as #15-Oct-2007" in the SQL - no ambiguity and
easier to read.

John
Hi,
I have a form that "builds" an SQL statement depending on the selections on
Click to expand...
[quoted text clipped - 18 lines]
AND
Format(IIf([lng_Priority]>2,[dte_TransportDate],[dte_BookingDate]),'dd/mm/yyyy') <= #11/10/2004#));
Click to expand...
Click to expand...
 
M

Marshall Barton

J_Goddard via AccessMonster.com said:
You can also express the date as #15-Oct-2007# in the SQL - no ambiguity and
easier to read.
Click to expand...
That will work for English based versions of Access, but Oct
is not unversally understood.

All language variations of Access will understand
#mm/dd/yyyy#
and
#yyyy-mm-dd#
 
W

Wolfgang Kais

Hello Marshall.

That will work for English based versions of Access, but Oct
is not unversally understood.

All language variations of Access will understand
#mm/dd/yyyy#
and
#yyyy-mm-dd#
Click to expand...

I have a german version of Access and it understands Oct. The
GUI won't understand it, I guess, but entering #15-Oct-2007#
as criterion in SQL view of a query, everything worked fine.

I think the problem was to programmatically create a sql string
that can be executed. It's no problem using the BuildCriteria method,
and it will be no problem when using #15-Oct-2007# in sql, but it will
be a problem to create the string #15-Oct-2007# in vba:
On my computer, Format(#10/15/2007#,"\#dd-mmm-yyyy\#") results in
#15-Okt-2007#, which is not understood in a sql string.
 
M

Marshall Barton

I think I was confusing the two issues. You're right that
dates enclosed in # should be ok using English month names.

Going out on a limb, I think this alternative might also be
ok (unless the wndows date format is set to something
strange)

sql = sql & "WHERE datefield=CDate(" &
Format(#10/15/2007#,"dd-mmm-yyyy") & ")"

Thinking about these issues gives me a headache. You
probably have plenty of experience with the idiosyncracies
of this stuff so it would be best if I refrain from leaping
to too many more conclusions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculate Age problem date notation european american format 2
yet another date format problem 1
date format problem 3
Problem with date format 1
Opening a csv file with US date format on a Australian PC 2
Q : Problem With Date Time Display Format 2
date format for data entry 2
Problem with Date format from VBA code 3

Top