Solved

Null values in parameter query

Posted on 2000-02-17
14
230 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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