Solved

Null values in parameter query

Posted on 2000-02-17
14
241 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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