Finding null

[Lars Brownie]

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

 

[Jeff Boyce]

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]

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

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

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

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

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

 

[Lars Brownie]

Though I messed up the query in my last post, you pointed me in the right
direction. Got it working now. 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

(no private e-mails, please)

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

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


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