Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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