Display found record OR display all records

[journeyman]

Hi all,



I am building a query to display a found record based on a string criteria - easy mode!

Except - If that query does not return a result, then I would like it to return all results (either table or by criteria)... including results where the criteria field is null.

So far I'm trying:

SELECT [CriteriaField], IIF([CriteriaField] LIKE '*ABC*', [CriteriaField], IIF([CriteriaField] Is Null, 'Empty Record', [CriteriaField]))
FROM MY_Table

However, this query returns all records in the table. I tried using the criteria field also...:

SELECT CriteriaField 
FROM My_Table
WHERE IIF([CriteriaField] LIKE '*ABC*', [CriteriaField], IIF([CriteriaField] Is Null, 'Empty Record', [CriteriaField]))

So.... neither works except to output the entire table....

I need either the result, or everything where the result failed - all tidy in one crazy query.

Any thoughts?

Cheers

[pbaldy]

Is this what you're after?

http://www.theaccessweb.com/queries/qry0001.htm

If you actually want to change the behavior when the query returns no records with a criteria, you'll probably have to test it in code are react accordingly.

[journeyman]

Thanks for your quick response.

Unfortunately, no. The following will cater to and display the NULL field.

IIF([CriteriaField] Is Null, 'Empty Record', [CriteriaField])

I am looking to either find a criteria result(s) .... OR return every record including any records that are null.

Thanks again

[pbaldy]

Not on a computer, but I think you'll have to test the SQL somehow, then return filtered or all records as appropriate. Knowing more specifics or seeing your current code would help.

[Minty]

I'm not quite grasping your expected results.
Which of these describes your desired outcome?

1. If no records match the criteria return all of the records.
2. If no records match the criteria return return only the records where the criteria field is null.
3. Return the records that match the criteria AND where the criteria field is Null
4. Return just the records that match your criteria

Some other combination I can't think of?

[DenShin]

I'm kinda having the same problem, however I'm using the expression builder, is there a way to get what I need?

1. Return the exact records that match the criteria, and returns all the record if the text box is empty or null.

For now, I'm using the Like "*" & txt.value & "*" upon my text box is empty, this display all the records on the database and returns only the records based from the value inside the text box. However, this has a negative side to it.

Example: I want to find all the records of my staffs. The records of RONALD and RONA.
Using the expression above, If RONALD is the value of the text box, this returns all records associated to RONALD. However, when I look for RONA's works the expression returns both RONALD and RONA's works.

Is there a way for needs to get?

Thank you in advance for the help.

[CJ_London]

See the link in post#2

[Welshgasman]

I'm kinda having the same problem, however I'm using the expression builder, is there a way to get what I need?

1. Return the exact records that match the criteria, and returns all the record if the text box is empty or null.

For now, I'm using the Like "*" & txt.value & "*" upon my text box is empty, this display all the records on the database and returns only the records based from the value inside the text box. However, this has a negative side to it.

Example: I want to find all the records of my staffs. The records of RONALD and RONA.
Using the expression above, If RONALD is the value of the text box, this returns all records associated to RONALD. However, when I look for RONA's works the expression returns both RONALD and RONA's works.

Is there a way for needs to get?

Thank you in advance for the help.

Do not use the leading * ?
However if they are matching for the same characters, that is always going to happen.

[DenShin]

Do not use the leading * ?
However if they are matching for the same characters, that is always going to happen.

Thank you for the response @Welshgasman, I've tried it and as you said, always going to happen. I also used the "IIF" still the same applies on it. Do you think I should make it on VBA code instead of the Expression Builder to get what I want for the result? If so, can someone help me how to make the code for it.
I have a "TableDatabase" and make "QueryData" out of the TableDatabase and I'm using the QueryData to display the records with the CRITERIA just like the above. I've been trying to make the code, but still I can't get it. The textbox im using is unbound one, this is the textbox where I input the CRITERIA to search on the records.

Tbl = Table
QDat = QueryData
Noted = Textbox

Again, thank you in Advance.