?
Solved

query with null values

Posted on 2002-07-30
15
Medium Priority
?
327 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
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!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

831 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