Query Date/Time field without opening design view

[kimb via AccessMonster.com]

I have a query that contains a Date/Time field. I am currently using a
between statement to get the results I am looking for.
Between #7/16/2009 6:00:00 PM# And #7/17/2009 1:00:00 PM#
Between #7/16/2009 4:00:00 PM# And #7/17/2009 1:00:00 PM#

(There are other criteria in the query, which is why there are more than one
between statement. There are actually 4 between statements, but for what I'm
asking I don't think it is necessary to post all of the criteria.)

Every day the person who runs this query has to go into the design view of
the query and change the dates (the times stay the same). I am trying to
avoid anyone going into the design view. I would like for a box to pop up and
say "Enter Yesterday's Date", and then "Enter Today's Date". I have tried
seperating the date and the time and putting them into their own fields.
However, when I go to search by time, instead of removing from 6:00pm
yesterday to 1:00pm today, it removes from 1pm to 6pm for all days. Since I
am eliminating data based on times, the times have to be connected to the
dates. Does anyone have any ideas as to how I could achieve this, or is it
even possible?

Thanks for your time!

 

[Dale_Fye via AccessMonster.com]

How about setting it up so they don't have to enter anything?

BETWEEN DateAdd("h", 18, Date() -1) AND DateAdd("h", 13, Date())

This would obviously not handle the 6PM on friday to 1PM on Monday (assuming
that is what you want). In that case, you would need to create the
appropriate parameters for your query. In Query design view, right click in
the gray area where the tables are located, and select the "Parmeters" option
from the popup menu. Then, in the Parameter column, enter [Start Day] and
[End Day], and in the Data Type column enter Date/Time. Then, refer to these
as:

BETWEEN DateAdd("h", 18, [Start Day]) AND DateAdd("h", 13, [End Day])

HTH
Dale

 

[Clifford Bass]

Hi Kim,

If you are using always yesterday and today, you can just use the
Date() function with some math:

Between Date() - 1 + #6:00:00 PM# and Date() + "1:00:00 PM#

For things that you do want to prompt for you can enter the prompt
within square brackets, in the criteria line:

[Enter Yesterday's Date:]

This will pop up a box with the text within the square brackets. Also,
you can tell Access the type of data the user should enter. Especially
important for dates and times. To do so, in Access 2003 and earlier go to
the Query menu and choose Parameters. Then enter the parameter, exactly as
used in the query in the left column and pick it's type in the right column.
In Access 2007 you can get to the same box by clicking on the Parameters item
in the Show/Hide section of the Query Tools, Design Ribbon. This is helpful
also if you use the same parameter in more than one spot in the query.
Defining the parameter eliminates the multiple requests for the same
parameter. Probably good practice to always define them.

Hope this helps,

Clifford Bass

 

[kimb via AccessMonster.com]

Thanks Dale! I think this will work!

Kim B.

How about setting it up so they don't have to enter anything?

BETWEEN DateAdd("h", 18, Date() -1) AND DateAdd("h", 13, Date())

This would obviously not handle the 6PM on friday to 1PM on Monday (assuming
that is what you want). In that case, you would need to create the
appropriate parameters for your query. In Query design view, right click in
the gray area where the tables are located, and select the "Parmeters" option
from the popup menu. Then, in the Parameter column, enter [Start Day] and
[End Day], and in the Data Type column enter Date/Time. Then, refer to these
as:

BETWEEN DateAdd("h", 18, [Start Day]) AND DateAdd("h", 13, [End Day])

HTH
Dale

I have a query that contains a Date/Time field. I am currently using a
between statement to get the results I am looking for.

[quoted text clipped - 17 lines]

Thanks for your time!

 

[shganduri]

Hi Kimb

From what you mentioned here, I have the following suggestion for you.
Please try this.

Open the query in design mode. Go to the date/time field and in the Criteria
row of the field type [Enter Yesterday's Date] and [Enter Today's Date]
When you start using this query, it will ask you to enter Yesterday's date
and after you enter that date it will again you to enter Today's Date and
then it works. You need not go to design mode each time. Good Luck.