Select Info between 2 Dates (w/e Date - 7 days)

[John]

I'm a complete Newbie at Access but want to learn.

Very simple question. I want to run a query that will list certain
transactions that fall between 2 dates (essentially its 1 week or 7 days).
The user will select via a drop down list an end date (then the query will
run from this date - 7)

I have created a Query that lists all Relevant info (qryInOutTimes) that I
need, I've also created a Table that the User can select via drop-down a
list of Week dates (tblDateSelect), now I want to incorporate into this
Query the Date selection.

I assume I run another Query that somehow uses my qryInOutTimes and
tblDateSelect, but how within my Criteria do I say "Only include info that
falls beween that date selected within tblDateSelect"

Hope someone can assist

FTU

 

[John Vinson]

I assume I run another Query that somehow uses my qryInOutTimes and
tblDateSelect, but how within my Criteria do I say "Only include info that
falls beween that date selected within tblDateSelect"

If Forms!yourformname!cboEndDate is a combo box which has a Date/Time
field as its bound column, you should be able to use a criterion

= DateAdd("d", -7, Forms![yourformname]![cboEndDate]) AND <= Forms![yourformname]![cboEndDate]

as a criterion on the date field.


John W. Vinson[MVP]

 

[Guest]

John:

Better would be:

WHERE TheDateField >= DateAdd("d", -7, Forms![yourformname]![cboEndDate])
AND TheDateField < DateAdd("d", 1, Forms![yourformname]![cboEndDate])

This would catch any rows with dates on the end date with non-zero times of
day in the date/time value (sometimes there unknown to the user through the
inappropriate use of the Now() function as a default value). The <=
operator would miss those as only values up to and including midnight at the
start of the last day would be caught. For the same reason the BETWEEN….AND
operator is best avoided when defining a date range.

Also its prudent to declare the parameters in the case of date/time data.

Ken Sheridan
Stafford, England

I assume I run another Query that somehow uses my qryInOutTimes and
tblDateSelect, but how within my Criteria do I say "Only include info that
falls beween that date selected within tblDateSelect"

If Forms!yourformname!cboEndDate is a combo box which has a Date/Time
field as its bound column, you should be able to use a criterion

= DateAdd("d", -7, Forms![yourformname]![cboEndDate]) AND <= Forms![yourformname]![cboEndDate]

as a criterion on the date field.


John W. Vinson[MVP]