Date select query

[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

 

[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]))

 

[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

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.

 

[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

 

[MikeR]

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.

 

[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.

 

[MikeR]

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

 

[Guest]

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.