• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

query with null values

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
da2121
Asked:
da2121
  • 7
  • 4
  • 2
  • +2
1 Solution
 
nico5038Commented:
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
 
nico5038Commented:
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
 
ildcCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
nico5038Commented:
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
 
da2121Author Commented:
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
 
ildcCommented:
Aren't they pretty "heavy" those unions?

regards
0
 
ildcCommented:
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
 
ildcCommented:
oops, submitted 2 fast by accident

should be

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

regards
0
 
nico5038Commented:
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
 
da2121Author Commented:
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
 
nico5038Commented:
Strange, are you sure that the criteria is filled with:
LIKE [reference to form combofield]

Nic;o)
0
 
KMANCommented:
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
 
nico5038Commented:
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
 
nico5038Commented:

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
 
ComTechCommented:
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now