?
Solved

query with null values

Posted on 2002-07-30
15
Medium Priority
?
319 Views
Last Modified: 2006-11-17
I'm running a query against a field in a subform; if a value is entered in the fielr, the query returns the expected records. Hoever, if the field is blank, I do not get any records.
I tried to paste the following
Wenn([Formulare]![Smpv_Adressen]![qrySektion] Ist Nicht Null;[Formulare]![Smpv_Adressen]![qrySektion])
into my query with no success.

any help appreciated
0
Comment
Question by:da2121
[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
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7187502
Try:

WHERE [Formulare]![Smpv_Adressen]![qrySektion]
when you want only the selected value or

WHERE IsNull([Formulare]![Smpv_Adressen]![qrySektion]) OR ([Smpv_Adressen] = [Formulare]![Smpv_Adressen]![qrySektion])

when you want all filled fields and empty ones.

Clear ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7187510
Oops, you intended to say that you want a selection for an empty field.
I guess you want all NULL fields to be displayed in that case.
Use in the criteria cell underneath the field:
IIF(IsNull([Formulare]![Smpv_Adressen]![qrySektion]);NULL;[Formulare]![Smpv_Adressen]![qrySektion])

Getting the idea?

Nic;o)
0
 
LVL 7

Expert Comment

by:ildc
ID: 7187533
Hi Nico and data2121,

As I understand data2121 wants to return everything if there's nothing filled in in the form field, so I think that the criteria line underneath the field should be something like this :
IIF(IsNull([Formulare]![Smpv_Adressen]![qrySektion]);LIKE'*';[Formulare]![Smpv_Adressen]![qrySektion])

regards


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 54

Expert Comment

by:nico5038
ID: 7187548
Could be right ildc, but in such a case I wouldn't use a field on a form this way, but I would create a combobox with a UNION query like:

SELECT "*" as field1, "All" as field2 FROM tblX
UNION
SELECT field1, field2 FROM tblX;

Now you can use in the query:
like Formulare]![Smpv_Adressen]![qrySektion]

Main advantage that you can't have an invalid value for  [Smpv_Adressen] as that would return nothing...

Nic;o)
0
 

Author Comment

by:da2121
ID: 7187562
none of the queries worked out; mine lookslike:
SELECT tbxAdressenSektionen.fkySektionId
FROM tbxAdressenSektionen
WHERE (((tbxAdressenSektionen.fkySektionId)=IIf([Formulare]![Smpv_Adressen]![FilterSektion] Is Not Null,[Formulare]![Smpv_Adressen]![FilterSektion])));
and doesn't work either.

I do not fully understand the last posting from nico5038;
what exatly does your proposed query doe?

i actually have a combobox:
SELECT Sektionen.pkySektionID, Sektionen.Sektion FROM Sektionen ORDER BY Sektionen.Sektion;
0
 
LVL 7

Expert Comment

by:ildc
ID: 7187566
Aren't they pretty "heavy" those unions?

regards
0
 
LVL 7

Expert Comment

by:ildc
ID: 7187568
Hi,

never use :
IIf([Formulare]![Smpv_Adressen]![FilterSektion] Is Not Null
Since no value can be equal to the null value. Use the ISNULL() function instead.

regards
0
 
LVL 7

Expert Comment

by:ildc
ID: 7187574
oops, submitted 2 fast by accident

should be

=IIf(isnull([Formulare]![Smpv_Adressen]![FilterSektion]),like "*",[Formulare]![Smpv_Adressen]![FilterSektion])))

regards
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7187591
The change of your combo query into:
SELECT "*" as pkySektionID, "<All>" AS Sektion FROM Sektionen ORDER BY Sektionen.Sektion
UNION
SELECT Sektionen.pkySektionID, Sektionen.Sektion FROM Sektionen ORDER BY Sektionen.Sektion;

Will place as first entry in the combo:
<All>

When you use now in the "resultquery":

LIKE forms("yourform")!comboname

Then the value or a "*" will be used and the "*" will show all.

Clearer ?

Nic;o)
0
 

Author Comment

by:da2121
ID: 7187678
Nico
I got the point on this; the dropdown shows up as expected. However I get an error about an expression beeing to complex when I choose <all>.

normaly, quoted values are strings; does that cause the problem. the field in question holds an integer.

Eugen

btw: thanks everybody trying to help; appreciate this!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7187757
Strange, are you sure that the criteria is filled with:
LIKE [reference to form combofield]

Nic;o)
0
 
LVL 5

Expert Comment

by:KMAN
ID: 7187892
Here is example from www.mvps.org/access

(Q) I'm using a control on a form as a parameter for my query. I've defined the criteria in the query as

Forms!myForm!myControl

How do I set this up so that if the field is left blank, all records are returned by the query?

(A) Change the criteria in the query to

Forms!myForm!myContrl OR forms!myForm!myControl Is Null

http://www.mvps.org/access/queries/qry0001.htm

K
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7187917
Nice solution KMAN, but when you have had a value in a field it's hard to reset it to NULL iso a zero-length string that's returned when using the Del key.

That's why I prefer the combo with <All> as that's the normal "windows GUI way" ;-)

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7286142

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7314794
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…
Suggested Courses

762 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