Problem with SQL? (A2k (9.0.4402) SR-1)

Topic

New Reply

Problem with SQL?

A2k (9.0.4402) SR-1

I have four records with the following record create dates:

11/07/01
11/08/01
11/08/01
11/09/01

This SQL returns all four records:

strSQLSELECT s.lngBPermitID INTO tblResultsBP
FROM qrytblBPermit s
WHERE s.dtmDateCreate >= #11/07/01#
AND s.dtmDateCreate = #11/08/01#
AND s.dtmDateCreate = #11/07/01#
AND s.dtmDateCreate <= #11/08/01# ORDER BY strPermitNo

What is wrong with the second and third SQL statement?

Thanks, John Graves

Reply | Quote

Replies

Hi John,
I can’t see anything obviously wrong with the SQL so I have a quick question – how were the dates entered? Is it possible that there is a time element in there that is not being displayed? That might account for the lack of records being returned.
Hope that helps.

Reply | Quote

Hi Rory

I’m running the SQL from a query, date field displays as 11/08/01 (I see no time), query is based on table with date field defined as m/d/yyyy 99/99/0000;0;_

Any other aresa to look at?

John

Reply | Quote

Hi John,
If you format the field in the query as Long Time does it display 00:00:00?

Reply | Quote

Hi Rory

WOW!

Another Rookie mistake, I’m updating Record Create and Update fields with Now()

1. What syntax in a update query can I use to remove the time and start using Date()?

2. How could I adjust the SQL to handle the date and time and continue using Now()?

John

Reply | Quote

Hi John,
Been there done that! You’re not alone….
You can update the dtmDateCreate field to Format([dtmDateCreate], “dd/mm/yyyy”) which should get rid of the time portion. (You can change the date format according to your needs.)
You could also adjust your query to use something like:
CreateDate: Format([dtmDateCreate],”dd/mm/yyyy”)
and set your date criteria for this field.
Hope that helps.

Reply | Quote

Have you tried changing AND to OR?

Reply | Quote

Hi,
That would return all records since every date is either = any given date.

Reply | Quote

Oops, sorry. Thanks, Rory.

Reply | Quote