Date select query

M

MikeR

I have a need to select records that have a date/time field, but I am only supplied
with a date. Is there something more elegant than "Select Mgr, LDate, CaseNum from
Log Where LDate BETWEEN #01-01-2008 00:00:00# AND #01-01-2008 11:59:59#"

Thanks,
Mike
 
J

John W. Vinson

I have a need to select records that have a date/time field, but I am only supplied
with a date. Is there something more elegant than "Select Mgr, LDate, CaseNum from
Log Where LDate BETWEEN #01-01-2008 00:00:00# AND #01-01-2008 11:59:59#"

Thanks,
Mike

I'd use a Form with an unbound textbox txtDate, and a criterion like
= CDate([Forms]![YourForm]![txtDate] AND < DateAdd("d", 1, CDate([Forms]![YourForm]![txtDate]))
 
M

MikeR

John said:
I have a need to select records that have a date/time field, but I am only supplied
with a date. Is there something more elegant than "Select Mgr, LDate, CaseNum from
Log Where LDate BETWEEN #01-01-2008 00:00:00# AND #01-01-2008 11:59:59#"

Thanks,
Mike

I'd use a Form with an unbound textbox txtDate, and a criterion like
= CDate([Forms]![YourForm]![txtDate] AND < DateAdd("d", 1, CDate([Forms]![YourForm]![txtDate]))
Thanks, John -
My apologies, I should have been more specific.
I've got a Jet BE and Delphi front end. What I need is a SQL select query, returning
a recordset.
 
J

John W. Vinson

My apologies, I should have been more specific.
I've got a Jet BE and Delphi front end. What I need is a SQL select query, returning
a recordset.

I don't know Delphi, but you should be able to construct a SQL string with the
desired date; add 1 (one day) to the date to get the next date, and use a
criterion such as
 
M

MikeR

John said:
I don't know Delphi, but you should be able to construct a SQL string with the
desired date; add 1 (one day) to the date to get the next date, and use a
criterion such as
Delphi is actually irrelevant. I'm talking to the BE with DAO. I don't see how the
date can be = and < at the same time. ;-)
SELECT *
FROM Log
WHERE mge = 'AZ1' and LDate = #01/01/1983# and LDate < #01/02/1983#

returns nothing.
 
J

John W. Vinson

Delphi is actually irrelevant. I'm talking to the BE with DAO. I don't see how the
date can be = and < at the same time. ;-)
SELECT *
FROM Log
WHERE mge = 'AZ1' and LDate = #01/01/1983# and LDate < #01/02/1983#

returns nothing.

The point is that tje 1/1/1983 in your query is actually 30317.0000000000,
and the #01/01/1983 11:45 am# in your table is really 30317.4895833333. Those
two numbers are NOT equal so the record is not returned.

You want to find dates for which the stored value is between 30317 and 30318,
or to be more precise, greater than or equal (>= is the symbol) than 30317 and
less than 30318.

WHERE mge = 'AZ1' and LDate >= #01/01/1983# and LDate < #01/02/1983#

or, equivalently,

WHERE mge = 'AZ1' and LDate >= #01/01/1983# and LDate < #01/01/1983# + 1

will get you your desired result.
 
M

MikeR

John said:
The point is that tje 1/1/1983 in your query is actually 30317.0000000000,
and the #01/01/1983 11:45 am# in your table is really 30317.4895833333. Those
two numbers are NOT equal so the record is not returned.

You want to find dates for which the stored value is between 30317 and 30318,
or to be more precise, greater than or equal (>= is the symbol) than 30317 and
less than 30318.

WHERE mge = 'AZ1' and LDate >= #01/01/1983# and LDate < #01/02/1983#

or, equivalently,

WHERE mge = 'AZ1' and LDate >= #01/01/1983# and LDate < #01/01/1983# + 1

will get you your desired result.

Thanks for the explanation John -
It's now firmly in the thick skull. Works a treat.
Mike
 
G

Guest

MikeR said:
Delphi is actually irrelevant. I'm talking to the BE with DAO. I don't see
how the date can be = and < at the same time. ;-)
SELECT *
FROM Log
WHERE mge = 'AZ1' and LDate = #01/01/1983# and LDate < #01/02/1983#

returns nothing.
 

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

Top