Access query

[AccessFrustrated]

I have a table with separate date and time fields which I've pulled
into a query. I need to query based on a start date and an end date,
however, on the end date I need it to pull only the activity up until
a certain time, i.e. 1:00 pm.

I've tried several different scenarios and I either get no data for
the end date or all data for the end date or all data for dates up
until the cut-off time on each day.

I've created an expression field in the query comprised of the date
and time fields, however, that does not solve the problem.

HELP!

Thank you.

 

[Clifford Bass]

Hi,

Several ways. Try this for one (you will need to ajust accordingly):

where [Date_Field] between #11/01/2008# and #11/19/2008# or
([Date_Field] = #11/20/2008# and [Time_Field] <= #1:00 pm#)

Or this one:

where [Date_Field] + [Time_Field] between #11/01/2008# and #11/20/2008
1:00 pm#

Clifford Bass

 

[AccessFrustrated]

Hi,

     Several ways.  Try this for one (you will need to ajust accordingly):

     where [Date_Field] between #11/01/2008# and #11/19/2008# or
([Date_Field] = #11/20/2008# and [Time_Field] <= #1:00 pm#)

     Or this one:

     where [Date_Field] + [Time_Field] between #11/01/2008# and #11/20/2008
1:00 pm#

                       Clifford Bass

I have a table with separate date and time fields which I've pulled
into a query.  I need to query based on a start date and an end date,
however, on the end date I need it to pull only the activity up until
a certain time, i.e. 1:00 pm.

I've tried several different scenarios and I either get no data for
the end date or all data for the end date or all data for dates up
until the cut-off time on each day.

I've created an expression field in the query comprised of the date
and time fields, however, that does not solve the problem.

Thank you.- Hide quoted text -

- Show quoted text -

Clifford,

Thank you, but will your suggestion work if I'm prompting for the
start date and then the end date and time?

Thanks!!
Darryl

 

[Clifford Bass]

Hi Daryl,

For that I would try the second one.

where [Date_Field] + [Time_Field] between [Enter Start Date:] and [Enter End
Date and Time:]

If that does not work, let me know.

Clifford Bass

Hi,

Several ways. Try this for one (you will need to ajust accordingly):

where [Date_Field] between #11/01/2008# and #11/19/2008# or
([Date_Field] = #11/20/2008# and [Time_Field] <= #1:00 pm#)

Or this one:

where [Date_Field] + [Time_Field] between #11/01/2008# and #11/20/2008
1:00 pm#

Clifford Bass

[snip]

Clifford,

Thank you, but will your suggestion work if I'm prompting for the
start date and then the end date and time?

Thanks!!
Darryl

 

[AccessFrustrated]

Hi Daryl,

      For that I would try the second one.

where [Date_Field] + [Time_Field] between [Enter Start Date:] and [Enter End
Date and Time:]

      If that does not work, let me know.

                   Clifford Bass

Hi,
     Several ways.  Try this for one (you will need to ajust accordingly):
     where [Date_Field] between #11/01/2008# and #11/19/2008# or
([Date_Field] = #11/20/2008# and [Time_Field] <= #1:00 pm#)
     Or this one:
     where [Date_Field] + [Time_Field] between #11/01/2008# and#11/20/2008
1:00 pm#
                       Clifford Bass

[snip]

Clifford,

Thank you, but will your suggestion work if I'm prompting for the
start date and then the end date and time?

Thanks!!
Darryl- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

That doesn't work either, the problem I think is that the date and
time fields are separated, but when I created an expression field in
the query it is not treating the field as a date & time, should I have
the expression field use date and time values instead?

 

[Clifford Bass]

Hi Daryl,

I think I have it. You are almost right. It is not realizing that the
date + the time is a date/time and is comparing to text. Try adding the
following line to the top of your query while in SQL view.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date and Time:] DateTime;

Oh, I have been presuming that your date field and time field are of
the Date/Time type. If that is not the case, then something different needs
to be done. Also, it really may make sense to just have one field because
all dates and times are really stored as combined date and time. With dates
the time is 0:00:00. With times the date is something like 12/30/1899
(Access's zero point for dates).

Hoping that will do it,

Cliff

 

[AccessFrustrated]

Clifford,

That did it!

Thank you!!

Hi Daryl,

I think I have it. You are almost right. It is not realizing that the
date + the time is a date/time and is comparing to text. Try adding the
following line to the top of your query while in SQL view.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date and Time:] DateTime;

Oh, I have been presuming that your date field and time field are of
the Date/Time type. If that is not the case, then something different needs
to be done. Also, it really may make sense to just have one field because
all dates and times are really stored as combined date and time. With dates
the time is 0:00:00. With times the date is something like 12/30/1899
(Access's zero point for dates).

Hoping that will do it,

Cliff

That doesn't work either, the problem I think is that the date and
time fields are separated, but when I created an expression field in
the query it is not treating the field as a date & time, should I have
the expression field use date and time values instead?

 

[Clifford Bass]

Hurray! You are welcome.

Clifford Bass