Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Null values in parameter query

Posted on 2000-02-17
14
Medium Priority
?
255 Views
Last Modified: 2008-03-06
I want to use a parameter query on a table. The query will specify three parameters. Two of which will always be present while the third may sometimes be null. When this third parameter is null I want it to return all records with a null value - except it doesn't work. I have tried the following code in the query to no avail.

IIF(ISNULL([Field 3:]), IS NULL, [Field 3:])

Using this code, if I enter a value for [Field 3:] it works fine. But when I leave [Field 3:] blank it doesn't match any records.

Any Ideas?

Thanks.
0
Comment
Question by:gwhite012597
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2532618
Can you use:

NZ([field 3:],vbnullstring)
or
NZ([field 3:])

This returns the value in the field unless the field is null, in which case it returns a zero-length string (1st example) or (from Access help):

"If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be empty in the fields that contain null values"

I'm pretty sure your solution will involve some usage of the NZ function.

Wes
0
 

Author Comment

by:gwhite012597
ID: 2532704
The use of NZ([Field 3:]) eliminates the use of the IIF statement but it would seem the problem lies with matching the null values in the records.
I have tried the following...
NZ([Field 3:], Is Null)
NZ([Field 3:], Null)
NZ([Field 3:])

None of these match the null values in the records.
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2532734
Hi gwhite,

In field names row for your Field3 type:
Expr1: NZ([Field3])
and in the criteria row type:
nz([Field 3:])

Cheers,
Dedushka
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Expert Comment

by:wesleystewart
ID: 2532738
This might be a little cludgy, but you could create two queries; one with three criteria and one with two criteria (that returns nulls for field 3:.  Then you could call whichever query had the number of criteria matching the number of filled-in fields in your form.  Sort of like:

If IsNull([field 3:] then
'call query with two criteria and "Is Null" as criteria for field 3:
else
'call query with three criteria
End if

Or you could test to see if a value is in field 3 and if it is null, substitute the string "Is Null" and use it as a criteria:

If Len(NZ([field 3:])) = 0 then
[field 3:] = "Is Null"
end if

Just some thoughts.

Wes
0
 
LVL 10

Accepted Solution

by:
paasky earned 1200 total points
ID: 2532790
Hello gwhite,

I would use Dedushka's suggestion with a minor change:

SELECT *
FROM table1
WHERE field1 = [Enter field1: ]
AND field2 = [Enter field2: ]
AND NZ(field1,"qwerty") = NZ([Enter field3: ],"qwerty");


SELECT *
FROM table1
WHERE field1 = [Enter field1: ]
AND field2 = [Enter field2: ]
AND NZ(field1,-999) = NZ([Enter field3: ],-999);

In my examples I replace null value with "qwerty" and 999, anyway it should be a string (text type field) or number (numeric field) that will not exists in the field3 if that has a value.

NZ([Field3]) is equal to 0 if Field3 is null.

Regards,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2532795
copy-paste typo...

SELECT *
FROM table1
WHERE field1 = [Enter field1: ]
AND field2 = [Enter field2: ]
AND NZ(field3,"qwerty") = NZ([Enter field3: ],"qwerty");


SELECT *
FROM table1
WHERE field1 = [Enter field1: ]
AND field2 = [Enter field2: ]
AND NZ(field3,-999) = NZ([Enter field3: ],-999);
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2532844
Hi paasky,
glad to see you again :-)
0
 

Author Comment

by:gwhite012597
ID: 2532847
paasky,

Wow, thanks for the clarification.
It seems to work now.

Thanks all for your help.

PS. Next question... Who deserves the points? :)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2532872
Dedushka, you too! You did sleep late today?! ;-)

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2532911
You should to make choice yourself :-)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2532917
gwhite, EE support can help you to split points if you feel so.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2593266
Glad we could help you gwhite.

Do you want me to give some points to Dedushka and wesleystewart because of their great effort?

Regards,
Paasky
0
 

Author Comment

by:gwhite012597
ID: 2593553
Firstly sorry for the delay in awarding the points. I sort of forgot about it after I got the thing going....
It is up to you if you want to award some points to the others. I gave you all of them because it was your answer that worked first-off without problems.

Thanks for your time,
gwhite
0
 
LVL 10

Expert Comment

by:paasky
ID: 2594754
Dedushka & wesleystewart:

I've posted questions for you. Thanks for co-operation. It's always pleasure working with you guys!

Paasky
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question