Trying to show all records when nothing shows up within a particular date range

[Gary Childress]

Trying to create a query criteria that will bring up all events within a date range. So here's what I have. I have two text boxes that are formatted for short date and have date pickers. The person entering the data can either use the date picker or just type in a short date like 2/1/15. I added the following criteria to the field in a query:

Between [Forms]![Switchboard]![Text501] And [Forms]![Switchboard]![Text502]

This works fine for me unless I leave text501 and/or text502 blank. If I leave the two fields blank, then the form comes up but I get a blank form with no records. Ideally I'd like to have all records appear when the fields are left blank.



Would this be possible? And if so what sort of code or expression would I put in the criteria box for the field in the query?

Thank you.

[orange]

It is helpful to readers to show your tables and relationships ( so we get some idea of the subject matter).

Your code could make sure the user has populated the text boxes.
You might research:
the CDate function ---converts a string to a Date
IsDate() function to ensure date field is valid eg no 48/3/2019
check the textbox for a value

iif (len(textfield & "")= 0, nothingEntered, check the date here)

[ssanfu]

You could try using something like http://access.mvps.org/access/queries/qry0001.htm

Between ([Forms]![Switchboard]![Text501] Or [Forms]![Switchboard]![Text501 Is Null) And ([Forms]![Switchboard]![Text502] Or [Forms]![Switchboard]![Text502] Is Null)

(I would give the controls better (more meaningful) names that the access default. "StartDate" vs "Text501"..... Which is more descriptive?)



I do not use a WHERE clause in the query. I create the filtering clause in code. http://allenbrowne.com/ser-62code.html

Depends on your VBA skills.

[Gary Childress]

You could try using something like http://access.mvps.org/access/queries/qry0001.htm

Between ([Forms]![Switchboard]![Text501] Or [Forms]![Switchboard]![Text501 Is Null) And ([Forms]![Switchboard]![Text502] Or [Forms]![Switchboard]![Text502] Is Null)

(I would give the controls better (more meaningful) names that the access default. "StartDate" vs "Text501"..... Which is more descriptive?)

I do not use a WHERE clause in the query. I create the filtering clause in code. http://allenbrowne.com/ser-62code.html

Depends on your VBA skills.

Thank you for the reply ssanfu. I tried the code you provided (inserted the missing bracket behind text501) but it still comes up with no records. Unfortunately my VBA skills are pretty much cut and paste only.

@orange: My apologies. I'm attaching a pic of the relationships if that helps. Also the date search is going to be optional so if a user wants to search by another criteria then they can do that instead and leave the date fields blank. So I don't want to require the user to enter something in the date field if their search doesn't require it. Thanks.