Issue with conditional expression in Access

user_8196779 0
Viewed 29

Essentially I would like to look at a form text box to determine what criteria to add to a SELECT Query in Access.

In a form I have 2 textboxes one representing the dateStart and one for dateEnd. If dateStart is empty the expression should default the criteria to todays date using Date(), and otherwise take the date range between the 2 text boxes.

The following is my current expression:

IIf(IsNull([Forms]![HomeForm]![dateStart]),Date(),Between [Forms]![HomeForm]![dateStart] And [Forms]![HomeForm]![dateEnd])

For some reason when the dateStart, dateEnd fields have a date I get no results.

Note: By itself the Between expression works with no issue, its only when inside the conditional that the issue occurs.

ms-access
1 Answers

The BETWEEN AND keywords cannot be conditional. Conditional needs to be on each parameter input. Use IIf() with IS NULL or Nz().

Between Nz([Forms]![HomeForm]![dateStart], Date()) And Nz([Forms]![HomeForm]![dateEnd],Date())

Alternatively, instead of dynamic paramterized query, use VBA to conditionally build filter criteria and apply to form or report.

user_76071900
Related