Solved

query with null values

Posted on 2002-07-30
15
315 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

740 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