Select all records when a field is null

[Tep]

Hi,

I have a subform and when field [Status] is Null I would like to append all records from table melding. I have some trouble with the criteria...

Does anybody understand why this does not work in an append query:

WHERE (((melding.status)=IIf([Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status] Is Not Null,[Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status],"*")))

The result in the datasheet is no records at all, and I expected all the records

However, the query does work correctly if I change the "*" into "Bij VWA":

WHERE (((melding.status)=IIf([Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status] Is Not Null,[Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status],"Bij VWA")))

The result in datasheet are all the records with [status] "Bij VWA".

It works also fine when I simplify it:

WHERE (((melding.status) Like "*"))

The result in datasheet are all the records.

But why doesn't the first where-clause work correctly???

I hope someone can help me with this!

Thanks in advance,
Tep

 

[pr2-eugin]

You have the answer in your hands.. In your last code, you have used Like operator, but on the first two you have not.. Simple as that.. instead of = use LIKE.. also for better computation use Nz instead of IIF.. something like,

WHERE (((melding.status) Like Nz([Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status],"*")))
[COLOR=Green]'BTW Nz() is a simplified If that checks only one condition IsNull or Not.. 
' Nz ( [I][B]field_to_check[/B][/I] , [I][B]value_if_Field_is_Null[/B][/I] )[/COLOR]

 

[Tep]

Great, Paul, this does it indeed!
I now used Nz:

 WHERE (((melding.status) Like Nz([Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status],"*")))

I still do not understand why "*" did not work and "Bij VWA" did work correctly (both without the like-operator). But well, with Nz-solution it works perfectly!
Thanks again.

 

[pr2-eugin]

When you use = (equals) operator it actually looks for a String that has the value *, but using the LIKE operator instructs access that * is not a String but a WILDCARD character with 0 or many characters.

When you used "Bij VWA", there actually was a String 'Bij VWA'.. that is the reason it returned the match.. Understood?

 

[Tep]

Ah, okay, Now I understand. Thank you for explaining.

 

[pr2-eugin]

You are most welcome..Have a good day..