Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Null values in parameter query

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
gwhite012597
Asked:
gwhite012597
  • 6
  • 3
  • 3
  • +1
1 Solution
 
wesleystewartCommented:
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
 
gwhite012597Author Commented:
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
 
DedushkaCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
wesleystewartCommented:
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
 
paaskyCommented:
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
 
paaskyCommented:
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
 
DedushkaCommented:
Hi paasky,
glad to see you again :-)
0
 
gwhite012597Author Commented:
paasky,

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

Thanks all for your help.

PS. Next question... Who deserves the points? :)
0
 
paaskyCommented:
Dedushka, you too! You did sleep late today?! ;-)

0
 
DedushkaCommented:
You should to make choice yourself :-)
0
 
paaskyCommented:
gwhite, EE support can help you to split points if you feel so.
0
 
paaskyCommented:
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
 
gwhite012597Author Commented:
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
 
paaskyCommented:
Dedushka & wesleystewart:

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

Paasky
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now