I have a field in a query that is checking how the user entered the date on the main form. I am trying to make it like a single entered date when the first text box is fill in and like a date range if an ending date is entered into the second text box. So if the ending date text box is blank, it should be ignored and the query should run as if filtering only on a specific date. I used the below expression and only entered the starting date, so it should only consider the field as being filtered by 1 date, not a range. But the query returns blank. [Text0] is the starting date and [Text3] is the ending date. The field is a job date field intended to only return job numbers from either that date or inside the date range.
Example: If [Text0] is set to 4/20/2015 and [Text3] is blank, the query should return job numbers A-18, B-18, and C-18. If [Text0] is set to 4/20/2015 and [Text3] is set to 4/27/2015, the query should return A-18, B-18, C-18, D-19, E-19, F-19. The difference between -18 and -19 is the week that it corresponds to.
=IIf((IsNull([Forms]![MainForm]![Text3])=True),[Forms]![MainForm]![Text0],Between [Forms]![MainForm]![Text0] And [Forms]![MainForm]![Text3])
I think what you need is something like this:
Select JobNumber, ...
from Jobs
where JobDate >= [Forms]![MainForm]![Text0]
and JobDate <= nz([Forms]![MainForm]![Text3],[Forms]![MainForm]![Text0])