"Between" Query parameter misbehaving

[JustinB]

Background:
- Query with a form to drive certain criteria
- The driving form has check boxes to create a Yes/No environment for which criteria a user can search from.
- two of the three parameters work fine with this formula.
- The formula sets up whether or not the check box is checked (0/1). If not checked, criteria uses a wild card to produce all results for that field, else it uses the user entered criteria.

Example of above:
Like IIf([Forms]![QRY-FORM]![Check-BN]=0,"*",[Forms]![QRY-FORM].[Base Number])

Issue
I have a Date field in which I would like to use the "between" formula. I have written the following formula with corresponding unbound fields in my form.

Like IIf([Forms]![QRY-FORM]![Date-CHK]=0,"*",([qry-TestData].[TestDate]) Between [Forms]![QRY-FORM]![StartDate] And [Forms]![QRY-FORM]![EndDate])

I cannot figure out why this is not working. regardless of how I enter the dates, the query produces no results for information that I know is within the scope of the parameter. The other two criteria fields are even blank, allwoing for wildcard searches.

If I'm missing any information that will help someone help me solve this, please let me know. Thank you in advance for your comments, suggestions and answers.

 

[Brianwarnock]

I don't think that you can use Like Between

Try

([Forms]![QRY-FORM]![Date-CHK]=0 or ([qry-TestData].[TestDate]) Between [Forms]![QRY-FORM]![StartDate] And [Forms]![QRY-FORM]![EndDate])

Brian

 

[JustinB]

Brianwarnock

Thanks. That worked. I haven't the foggiest idea how or why, but it worked.

 

[Brianwarnock]

If the date-CHK is 0 then that is true for all records so all are selected, if not true then the Between etc is-applied

Brian