Date Range parameters

[AlCamp]

When working with text parameters for a query for a report, I use...
Like [Enter ProductID] & "*"
That way, the user can enter a ProductID to return a specific Product,
partial ProductID to return a "range" of Products, or leave blank to return
ALL Products. Works great for text!!

I need to translate that kind of parameter "flexibility" to a Date field.

I have a "criteria" form that delivers parameter values to the query for a
report.
On that form the user can specify a date range, and... via an OptionFrame
with 2 checkboxes, tell the report query to return records in the Date
Range, or return ALL dates.

FormName = frmDialog
Date range fields on frmDialog = [StartDate] & [StopDate]
OptionFrame = [RangeOrHistory]
Checkbox [Range] = 1 (Use the date range on this form)
Checkbox [History] = 2 (returns ALL dates)
The Date field in the query = [RcvdDate]

What would be the best way to code that query to properly deliver the
records I want? Can I use an IIF statement in the query Criteria for
[RcvdDate]... or is there a more elegant or direct solution?

Thanks for any assistance,
Al Camp

 

[Marshall Barton]

When working with text parameters for a query for a report, I use...
Like [Enter ProductID] & "*"
That way, the user can enter a ProductID to return a specific Product,
partial ProductID to return a "range" of Products, or leave blank to return
ALL Products. Works great for text!!

I need to translate that kind of parameter "flexibility" to a Date field.

I have a "criteria" form that delivers parameter values to the query for a
report.
On that form the user can specify a date range, and... via an OptionFrame
with 2 checkboxes, tell the report query to return records in the Date
Range, or return ALL dates.

FormName = frmDialog
Date range fields on frmDialog = [StartDate] & [StopDate]
OptionFrame = [RangeOrHistory]
Checkbox [Range] = 1 (Use the date range on this form)
Checkbox [Forms!frmDialog.] = 2 (returns ALL dates)
The Date field in the query = [RcvdDate]

What would be the best way to code that query to properly deliver the
records I want? Can I use an IIF statement in the query Criteria for
[RcvdDate]... or is there a more elegant or direct solution?

Try adding this to the query's WHERE clause:

.. . . AND
((RcvdDate Between Forms!frmDialog.StartDate And
Forms!frmDialog.StopDate) OR (Forms!frmDialog.RangeOrHistory
= 2))

If you must work within the QBE grid, add the above as a
calculated field with the criteria of True.

 

[AlCamp]

Thanks Marshall,
It's going to take me a little time to try this out, but I'll try to get
back to you as soon as I can.
I appreciate the help very much, and your code looks like just what I
want...
These Access NGs are an invaluable resource!!
Thanks,
Al Camp

When working with text parameters for a query for a report, I use...
Like [Enter ProductID] & "*"
That way, the user can enter a ProductID to return a specific Product,
partial ProductID to return a "range" of Products, or leave blank to
return
ALL Products. Works great for text!!

I need to translate that kind of parameter "flexibility" to a Date field.

I have a "criteria" form that delivers parameter values to the query for a
report.
On that form the user can specify a date range, and... via an OptionFrame
with 2 checkboxes, tell the report query to return records in the Date
Range, or return ALL dates.

FormName = frmDialog
Date range fields on frmDialog = [StartDate] & [StopDate]
OptionFrame = [RangeOrHistory]
Checkbox [Range] = 1 (Use the date range on this form)
Checkbox [Forms!frmDialog.] = 2 (returns ALL dates)
The Date field in the query = [RcvdDate]

What would be the best way to code that query to properly deliver the
records I want? Can I use an IIF statement in the query Criteria for
[RcvdDate]... or is there a more elegant or direct solution?

Try adding this to the query's WHERE clause:

. . . AND
((RcvdDate Between Forms!frmDialog.StartDate And
Forms!frmDialog.StopDate) OR (Forms!frmDialog.RangeOrHistory
= 2))

If you must work within the QBE grid, add the above as a
calculated field with the criteria of True.