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

On Wed, 29 Oct 2008 16:55:21 -0400, MikeR <(E-Mail Removed)> wrote:

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

--

John W. Vinson [MVP]

John W. Vinson wrote:
> On Wed, 29 Oct 2008 16:55:21 -0400, MikeR <(E-Mail Removed)> wrote:
>
>> 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.

On Wed, 29 Oct 2008 17:57:46 -0400, MikeR <(E-Mail Removed)> wrote:

>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

>= {the selected date} AND < {the selected date} + 1

--

John W. Vinson [MVP]

John W. Vinson wrote:
> On Wed, 29 Oct 2008 17:57:46 -0400, MikeR <(E-Mail Removed)> wrote:
>
>> 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
>
>> = {the selected date} AND < {the selected date} + 1
>
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.

On Wed, 29 Oct 2008 20:29:56 -0400, MikeR <(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]

John W. Vinson wrote:
> On Wed, 29 Oct 2008 20:29:56 -0400, MikeR <(E-Mail Removed)> wrote:
>
>> 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.

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

"MikeR" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> John W. Vinson wrote:
>> On Wed, 29 Oct 2008 17:57:46 -0400, MikeR <(E-Mail Removed)> wrote:
>>
>>> 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
>>
>>> = {the selected date} AND < {the selected date} + 1
>>
> 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.