WHERE (MyTable.Straat=[forms]![MyForm].[Street]) AND
(MyTable.HousNr=[forms]![MyForm].[HouseNrAddition] OR
([forms]![MyForm].[HouseNrAddition] IS NULL AND MyTable.HousNr IS NULL)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Lars Brownie" <lars@brownie.com> wrote in message
news:g68c4n$ct9$1@textnews.wanadoo.nl...
>> Null means "nothing there". Is part of the problem because you are
>> trying to compare to "nothing there"?
>
> Yes. Here is a shortened example:
>
> SELECT MyTable.Member_Name, MyTable.Street, MyTable.HouseNrAddition
> FROM MyTable
> WHERE (((MyTable.Straat)=[forms]![MyForm].[Street]) AND
> ((MyTable.HousNr)=[forms]![MyForm].[HouseNrAddition]));
>
> In a record of MyTable for instance, Street has value MyStreet and
> HouseNrAddition has value Null. If the corresponding form fields have
> similar values, there is no match. I tried several iif's but can't get it
> to work. It does work when HouseNrAddition field on the form is not null.
>
> Thanks, Lars
>
> "Jeff Boyce" <nonsense@nonsense.com> schreef in bericht
> news:O18E1KR7IHA.1196@TK2MSFTNGP05.phx.gbl...
>> Null means "nothing there". Is part of the problem because you are
>> trying to compare to "nothing there"?
>>
>> If you'll post the SQL statement of your query, the folks here would have
>> a few more clues to go on...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Lars Brownie" <lars@brownie.com> wrote in message
>> news:g689fl$c1u$1@textnews.wanadoo.nl...
>>>I have a query with criteria in 2 fields:
>>> FieldA has criteria: [forms]![MyForm].[Field1]
>>> FieldB had criteria: [forms]![MyForm].[Field2]
>>>
>>> So I need an exact match on those 2 fields.
>>>
>>> Field1 is never null but Field2 can be null. If Field2 is null then the
>>> query should look for the Field1 criteria in FieldA AND for a null value
>>> for FieldB. However, this is not working, I get no results. When field2
>>> is not null, it does work.
>>>
>>> Can someone help me out?
>>> Thanks, Lars
>>>
>>
>>
>
>