Solved

query with null values

Posted on 2002-07-30
15
311 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now