I've not used Access for some time and have been asked to build a database for the company I work for.

What they would like is to be able to run reports based on criteria from a form. A sort of simplified customisable report
The problem I have encountered is if the value of the field is null then do not apply the criteria.

Field is using a look up table and displays the value but contains the id. ie 1 = Apple, 2= Pear etc.

In the criteria I have used an IIF statement that looks up the value in the field and if its Null I want it to show all records but if it contains a value only show those records that match the value.

I have tried a number of things but just can not get the true part of the statement to work.

IIf([Forms]![Frm_CustomBasicReport]![Combo4] Is Null,{True part},"![Forms]![Frm_CustomBasicReport]![Combo4]")

The value of Combo4 would only be null or a number (yes I do know that Combo4 is not good practice but this is just to test that it will work).

If anyone has any ideas I would be truely greatfull and those around me won't have to put up with me swearing at the computer all day!

PS I'm more rusty on VBA and to be honest wasn't very good when I used to us Access all the time.

Thanks

Use IsNull(fieldname) not fieldname Is Null

Hi

Welcome to UtterAccess

If you have your lookup table as part of the query using a LEFT JOIN (you can set that in query design view), you can display the value instead of the ID but also filter on the ID. The criteria for the ID field would be something like

Thanks for reminding me about the IsNull ( I did say I was rusty) this has helped a lot but its still the true part that I'm having problems with. If the IsNull is true I need the search to be for everything if its false then the value in the field is used.

The false part works perfectly but what ever I try in the true part if fails to show any records.