Select Query on Criteria or Blank?

[batowl]

Hi Everyone,



I am trying to build a select query that looks up records in Table A. I am trying to allow the end users of the database to select the criteria for this query by filling in data into Table B and having Table A & B related. So far, this is working fine by telling my query to return values in Field 1 from Table A that match the values in Field 1 of Table B.

However, I want to give the end users the option of leaving a data field in Table B blank, thereby not specifying any criteria and returning all values in Table A. That is where I am running into a problem. Any suggestions?

[Rhino373]

I'd measure the recordcount of table B and if it is zero, I'd run a different SELECT query that returns all of table A.

[batowl]

Isn't there some way of structuring the one query so that it returns either records in Table A that match the criteria set up by Table B OR returns all of Table A when Table B IS Null?

[batowl]

Ok, I think I am getting close here. In the query criteria I have the following expression:

IIf(IsNull([Date Query].[Exposure Setting])," ",[Date Query].[Exposure Setting])

Right now this should be saying: if the exposure setting field in the Date Query Table is blank, then return a blank space, if it is not null, return the value in the exposure setting field of the Date Query table.

Is there a way to fill in that " " so that if this field is null, it returns everything in this field? Something along the lines of using "Like" or an *... I cannot figure out exactly how to tell it to return everything...

[batowl]

Ok, solved, temporarily at least. The solution is:
[type prompt here] Or Like [repeat prompt here] Is Null