Query to return all record when nothing is entered

[hooi]

I have a selectin window for a report that if nothing is entered for the field, all records will be displayed in the report. The query I've created is as follow:

IIf(IsNull([forms].[fmVSSelection].[VendorService ID]),Like "*",[forms].[fmVSSelection].[VendorService ID])

However it doesn't work when the field is left blank.

I've also tried:
IIf([forms].[fmVSSelection].[VendorService ID] is null,Like "*",[forms].[fmVSSelection].[VendorService ID])

Neither does this work.

What should I do?

Thanks in advance for helping.

 

[hooi]

The [VendorService ID] is a numeric field.

 

[AncientOne]

Like "*" & [forms].[fmVSSelection].[VendorService ID] & "*"

 

[hooi]

Hi AncientOne,

Thank you for your suggestion, it still doesn't work though.

 

[dcx693]

Hooi, since your form field contains numeric data, try a criteria expression like this:
[forms].[fmVSSelection].[VendorService ID] Or Like [forms].[fmVSSelection].[VendorService ID] Is Null

To be truthful, I'm not sure why it works, but it does. I got the tip off Martin Green's Office tips website: http://www.fontstuff.com/index.html.

 

[hooi]

Great! It works. Thank you.

 

[hooi]

I noticed the result of my query from the selection window is non-exclusive, for eg:

if I have two selection fields:
VendorService ID,
Network Type.

The table has the following data (underlines added for formating):
VendorService ID ____Network Type
1_________________ISDN
1_________________ATM
1_________________Leased
2_________________ISDN

If '1' and 'ISDN' is supplied in the selection window, the result I get now is:
1_________________ISDN
1_________________ATM
1_________________Leased
2_________________ISDN

I actually want the result to be:
1_________________ISDN

How should the query be modified to make the selection value supplied to be "AND" instead of "OR" as is the case now.

Thanks for helping...

 

[Jon K]

Try these in two columns in the query grid:-

------------------
Field: IIf(IsNull([forms].[fmVSSelection].[VendorService ID]),True,[VendorService ID]=[forms].[fmVSSelection].[VendorService ID])

Show: uncheck
Criteria: <>False

------------------
Field: IIf(IsNull([forms].[fmVSSelection].[Network Type]),True,[Network Type]=[forms].[fmVSSelection].[Network Type])

Show: uncheck
Criteria: <>False

 

[hooi]

Awesome! It Works! Thank you Jon K.

 

[Rose412]

Why won't Martin Green's tip work when a second criteria is added?

What do those "True" and "<>False" do in Jon K's?

I tried very hard to find the answer or get some hint from the Access' Help but to no avail.

 

[Jon K]

Putting <>False in the Criteria: cell is one of Access's query design defaults. It tells Access to treat the expression in the Field: cell as a criteria (criterion?).

Since True<>False is a universal truth, when nothing is entered in the textbox on the form, the True in the IIF expression will return every record.


When you follow Martin Green's tip and put [forms].[fmVSSelection].[VendorService ID] Or [forms].[fmVSSelection].[VendorService ID] Is Null

in the Criteria: cell, after the query is saved, Access will put the second part of the criteria in a new column:

Field: [forms].[fmVSSelection].[VendorService ID]
Show: uncheck
Or: Is Null

You can see that when nothing is entered in the textbox, this:-
Or [forms].[fmVSSelection].[VendorService ID] Is Null

becomes True, and so every record will be returned. So it works.


However when a second criteria is added, it will result in 4 columns and Access won't know how to join them correctly with AND and OR.


Hope this helps.

 

[Rose412]

Thank you, Jon K.

I did read somewhere that we could use a Select statement with "Where False" to suppress the display of records. So "Where True" is just the opposite and will return every record.

Thanks again.