Filtering and Display of Multiple Date Fields

[stevekroll]

I am using Access 2010. I am trying to create an activity report for a specific period(monthly, quarterly) based on a query that has several date fields per record in the table. Each date field is a due date for a report/filing requirement. So the activity report should show the due dates for those dates falling between the start and end dates of the query/report. For each date field, dates falling before or after the date range should be excluded. In design view, when one of the date fields is filtered for the date range using the date function, that particular date field column is correctly filtered, but the other date fields show dates prior and after the date range because the record contains all the date fields not just the one filtered. The other date fields should be shown, but I don't want dates outside the date range displayed. In design view, should I filter each date field for the date range?

[June7]

Filtering does not exclude or include fields, filtering is applied to include or exclude records. Whether or not a date field shows its value would have to be accomplished with an expression using the field. That expression can be done in query or textbox on report or maybe even with Conditional Formatting.

How are the start/end date parameters specified? Are they entered in controls on form?

[stevekroll]

The start/end parameters were specified in the Criteria section in design mode. What type of expression would be entered to not show a value in a field and where do you enter it?

[June7]

Expression can be in query to construct a field. Expression would be on the Field row of query. Access Help has more info on using expressions in query. Expression can be in ControlSource property of textbox on form or report.

Example:

IIf([datefield]<[start date] Or [datefield]>[end date], Null, [datefield])

Options for specifying the start and end dates:

1. hard coded in the expression

2. if expression in query, can be popup prompts

3. enter values in textboxes on a form and reference the controls as input parameters