iif condition with null function

[shakeelmscw]

I want to list all the records which contains data as per my filter form field company.

i have tried this

Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")

problem is this i want to display all the records including null, but this code only show all records except null

Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")

 

[shakeelmscw]

IIf(IsNull([Forms]![Filter]![COMPANY]),Like "*" Or Is Null,Like "*" & [Forms]![Filter]![COMPANY] & "*")

tried this also but zero records:banghead:

 

[JHB]

Try the below:

Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*") OR Is Null

 

[shakeelmscw]

I want the null only when my form field contain null. Can you revise?

 

[Brianwarnock]

Like does not return Nulls

Where myfield = forms!filter!company or forms!filter!company is null

If there is data in the filter then only matches will be returned else if null all records including nulls will be returned.

The filter form textbox company could be a be combo

Brian

 

[shakeelmscw]

Sorry I couldn't explain well

I want query should display all records including null only when form dropdown field doesn’t contains any selection. Once we select any value from drop down it should only display the value excluding null.

Actually I have multiple drop down on filter form based on a table. My table data contain many fields with null value. Once I run query many drop down contain null value and one or two contain selection. So result comes empty because all conditions are not met.

 

[Brianwarnock]

I want query should display all records including null only when form dropdown field doesn’t contains any selection. Once we select any value from drop down it should only display the value excluding null.

That is what will happen with the code I gave.

Brian

 

[Brianwarnock]

You might like to read this thread for more ideas/ info.

http://www.access-programmers.co.uk/forums/showthread.php?p=1251670#post1251670

Brian

 

[shakeelmscw]

I have tried single (on one field in query) and it is working exactly when i want.

Like "*" & [forms]![Filter]![COMPANY] & "*" Or [forms]![Filter]![COMPANY] Is Null

but when i apply 2nd condition it does not display any thing. i mean result is empty.


Like "*" & [forms]![Filter]![CITY] & "*" Or [forms]![Filter]![CITY] Is Null

Can you tell me why ??

 

[Brianwarnock]

Did you And the criteria, perhaps you can post your SQL.

Personally I would be inclined to take my search criteria City, Company etc from combo boxes based on my table and use the first form of the criteria that I posted as you will not need partial searches.

See post 2 here as he is doing similar to you

http://www.access-programmers.co.uk/forums/showthread.php?p=1287623#post1287623

Brian

 

[shakeelmscw]

Did you And the criteria, perhaps you can post your SQL.

Personally I would be inclined to take my search criteria City, Company etc from combo boxes based on my table and use the first form of the criteria that I posted as you will not need partial searches.

See post 2 here as he is doing similar to you

http://www.access-programmers.co.uk/forums/showthread.php?p=1287623#post1287623

Brian

Thanks a lot, your code was perfect, i did not checked carefully for other column in criteria, their was old code and i removed and everything is perfect.

Bundle of Thanks.