Finding null

L

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
 
J

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
 
L

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

Jeff Boyce said:
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 said:
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
 
D

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)


Lars Brownie said:
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 said:
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 said:
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
 
L

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

Douglas J. Steele said:
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)


Lars Brownie said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top