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

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
>

> 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
>>
>
>

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
>>>
>>
>>
>
>

Though I messed up the query in my last post, you pointed me in the right
direction. Got it working now. Thanks!
Lars

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
news:%23LvnKxR7IHA.4108@TK2MSFTNGP04.phx.gbl...
> 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
>>>>
>>>
>>>
>>
>>
>
>