Using Forms!FormName!Control Is Null in criteria

[Newbie100]

I learned the criteria on this forum
Forms!FormName!Control or Forms!FormName!Control Is Null

It enables me to leave the text box on a search form blank to return all records. This seems to work but has a very odd behaviour.

In design view, I typed the criteria for 3 fields and saved the query, but when I reopened it, the criteria added 3 NEW COLUMNS and spread over 8 ROWS (as in the image attached.)

The SQL was even unreadable:

SELECT Table1.ID, Table1.FldA, Table1.FldB, Table1.FldC
FROM Table1
WHERE (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC])) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR ((([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null));


I don't know how to add the criteria for the 4th field.

Can someone help?

Thanks

 

[wazz]

the 3 columns on the left look the same as the 3 columns on the right, just inverted (Is Null on the right becomes a blank on the left).

but the 3 columns on the right have form contol-names in the field-row which is wrong.
the 3 columns on the left have actual fields in the field row and form control-names in the criteria which is right.

 

[Newbie100]

but the 3 columns on the right have form contol-names in the field-row which is wrong.

That's why I called it bizarre. Originally I just typed the following three respectively in the criteria cells for FldA, FldB and FldC:-

[Forms]![Form1]![txtA] or [Forms]![Form1]![txtA] Is Null
[Forms]![Form1]![txtB] or [Forms]![Form1]![txtB] Is Null
[Forms]![Form1]![txtC] or [Forms]![Form1]![txtC] Is Null

What you see now in the image was actually changed by Access itself after the query was saved.

The query runs fine. But adding the extra three columns and spanning the criteria into 8 rows by Access makes it impossible for me to add the criteria for the 4th field in Design or SQL.

Is there anyway or option setting in Access that can stop it from changing what I typed in the criteria cells when saving?

Thanks.

 

[wazz]

oic. i think the prob could be how you used Is Null. you've tacked it on the end.

(i) it's not being read properly (or maybe it is if you're getting what you need!). IsNull is used like this: IsNull(item), and
(ii) (this might not be quite right) it's being read as a separate criteria unrelated to a textbox.

perhaps you need this:

[Forms]![Form1]![txtA] or IsNull([Forms]![Form1]![txtA])

- it looks like this will give you txtA no matter what is in it, including a nullstring ("").

or

1st criteria row under respective field: [Forms]![Form1]![txtA]
2nd criteria row under respective field:[Forms]![Form1]![txtB]
3rd criteria row under respective field:[Forms]![Form1]![txtC]

- leaving out the all of the 'isnull' parts. if you are really checking for nulls this might not work as there could be a "" in a txtbox. i've had that problem before when creating a search form and trying to clear fields. maybe the 1st idea above will work or maybe you have exactly what you need?! interesting.

anyone else?
please post back with updates. tnx.
hth.

 

[Newbie100]

Thanks. I tried your suggestions, but no joy.

1st criteria row under respective field: [Forms]![Form1]![txtA]
2nd criteria row under respective field:[Forms]![Form1]![txtB]
3rd criteria row under respective field:[Forms]![Form1]![txtC]

joined the criteria with ORs and returned unwanted records.

And IsNull() in
[Forms]![Form1]![txtA] or IsNull([Forms]![Form1]![txtA])
[Forms]![Form1]![txtB] or IsNull([Forms]![Form1]![txtB])
[Forms]![Form1]![txtC] or IsNull([Forms]![Form1]![txtC])

did leave the criteria unchanged when the query was saved, but when the query was run, nothing was returned when any of the boxes was left blank.


According to this link,
http://www.access-programmers.co.uk/forums/showthread.php?t=135364
it seems the change by Access (or restructure as it's called in the link) is automatic and unavoidable.

So it seems what I need is a solution that can prevent Access from restructuring the criteria. Anyone?

Thanks.

 

[EMP]

So it seems what I need is a solution that can prevent Access from restructuring the criteria.

Jon K addressed this issue and posted such a solution in the Samples forum back in March 2006. He used what he called the Basic Criteria Format in a new column in query design:
----------------------------------------
Field: [FieldName]=Forms!FormName!Control OR Forms!FormName!Control Is Null

Show: uncheck

Criteria: True
----------------------------------------

You can read his thread and Notes (1) and (2)
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Note (1) is about the issue of using your criteria.

Note (2) is about the issue of using a similar criteria where one can leave the control blank:
Like "*" & Forms!FormName!Control & "*"

^

 

[wazz]

ah. tnx EMP.

 

[Newbie100]

Thank you very much, EMP

Problem solved.
Appreciated your help.