Shipping Dates Query

[Guest]

I have a query set up for the user to look up data between two dates. I
wanted to know if there was any way for the parameter query to have buttons
such as the last 90 days, last 180 days, Last 365 days. Any help would be
greatly appreciated.

Donbenz

 

[MGFoster]

I have a query set up for the user to look up data between two dates. I
wanted to know if there was any way for the parameter query to have buttons
such as the last 90 days, last 180 days, Last 365 days. Any help would be
greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The QueryDef can't have any command or option buttons. You'd have to
set those up on a form & run the query from the form. Then use form
reference parameters to filter the query. E.g.: (SQL View)

PARAMETERS Forms!frmCriteria!txtDays SHORT;
SELECT ...
FROM ...
WHERE date_column >= Date() - Forms!frmCriteria!txtDays

The PARAMETERS data is a reference to an open form named "frmCriteria"
and the control named "txtDays." When the value in txtDays is 90 the
WHERE clause expression would yield a date of 3/24/2006 (Date() =
6/22/2006).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJraBoechKqOuFEgEQJA3gCg97ZLXIYOPjhoXw+Ic1rgKJ4OxXYAoOX7
7NY0kERG4Mby+2GKFryrnhCM
=NdJ7
-----END PGP SIGNATURE-----

 

[Guest]

First, if you will have just those 3 options, I would suggest using an option
group with 3 option buttons. Each labeled with the number of days and each
with an Option Value property equal to the number of days. You can use this
to tell the function below how many days back to look.

As to the filtering, it could be done in the query; however, I prefer to use
functions if criteria has much logic to it. It this case, there is not a lot
of logic, but I am more comfortable handling dates in VBA. So, in your
query, create a calculated field to call the function and pass the date to
it. The set the Criteira for this field to True.
The Field in the query builder would look like:
IncludeMe: InDateRange([ShipDate])

Function InDateRange(dtmShipDate As Date) As Boolean
Dim dtmEarlyDate As Date
Dim dtmLateDate As Date

dtmLateDate = date
dtmEarlyDate = DateAdd("d", -[Forms]![MyFormName]![MyOptionGroupName], _
dtmLateDate)
InDateRange = dtmShipDate >= dtmEarlyDate And dtmShipDate <= dtmLateDate

End Function