filter for "leftover" values

[catbert]

Hi,

I have a form used to generate query output. The user selects criteria in a simple combobox on the form. It all works apart form the "other" selection.

I have a list of types of work - and one needs to be other - when selected I want the query to show everything "leftover". To make this work I have a hidden unbound text field which populates based on the combo box selection - so if you pick A B or C the unbound textbox says A B or C and this works fine in the query criteria. But if the user selects "D - Other" the unbound text box is set to null and I want everything except A B or C.

In the criteria for the appropriate field I have this ( I have taken out full form/form name references and renamed objects for clarity):

IIf(IsNull[txtUnboundDiplay])=True, "<> "A" and <> "B" and <> "C"", txtUnboundDiplay)

but it doesn't work. Can anyone help as I thought my logic was sound and I'm not sure what the problem is. Using <> "A" and <> "B" and <> "C" works fine on it's own as criteria.

Thanks in Advance
CB

 

[Golom]

In the query designer you can specify things like

<> 'A' AND <> 'B' AND <> 'C'

as criteria for a field. That however gets translated to

fld <> 'A' AND fld <> 'B' AND fld <> 'C'

and that is the correct form. So you need

IIf(IsNull[txtUnboundDiplay]), 
fld <> 'A' and fld <> 'B' and fld <> 'C', 
txtUnboundDiplay)

 

[catbert]

Hi Golom,

thanks for the quick response, what you say makes perfect sense, especially when I took the time to look at the SQL - but I have tried the following and I still get nothing returned, here it is in full

IIf(IsNull([Forms]![frmPickForm_Estab_Recycling_PickArea]![txtRecyclingTypeForReportHeader])=True,[ContainerType]<>"paper",[Forms]![frmPickForm_Estab_Recycling_PickArea]![txtRecyclingTypeForReportHeader])

I tried just one <> as a start - of course if I just use <> paper I get the expected results.

Any thing else you can suggest would be gratefully snapped up
CB

 

[randy700]

How about something like this in your query criteria...

=iif(IsNull([i]YourFormName!HiddenControlName[/i]),NOT IN('A','B','C'),[i]YourFormName!HiddenControlName[/i])

Note: This has NOT been tested.

Randy

 

[catbert]

Hi Randy,

thanks but that doesn't work either. Testing with NOT IN ("A","B","C") again works perfectly but using the whole thing returns nothing, I have tested the text box on the unbound form and it is definitely null when it should be.

Both these solutions should work I'm sure. I'm at my wits end with this. If anyone can put me out of my misery I'll be very grateful.

CB

 

[randy700]

Create a public function that determines the value you want to use in your criteria. Something like...

Public Function GetMyValue(strX As String) As String
code here
GetMyValue = "A"
End Function

In your query, set the criteria as...
=GetMyValue(TextBox Value)


Randy

 

[catbert]

Thanks for the reply again,

I cheated in the end - I created a linked table applying a code to each type - then anything other just filters on code O, not clever but it worked and I had to get the report out.

Now the pressure is off I will look at your last suggestion - but I would love to know why it didn't work otherwise! Very strange.

thanks again
CB

 

[randy700]

Not entirely sure, but I think the reason your original code didn't work as you expected is because you were testing for a null value in the text box. In all probability, the text box value was actually "", which is NOT the same as null.


Randy

 

[catbert]

No, I checked that and it was definitely null, if I trasnfer the criteria into a field expression it retuns true for field being null - I just can't find a way of using that. It works with the additioanl code table so I guess it will do. Just frustrating when logic seems to fail. Thanks for all the help though, helps keep my sanity to share the pain!

 

[PHV]

Have you tried something like this (SQL code)?
SELECT ...
FROM ...
WHERE (
ContainerType=[Forms]![frmPickForm_Estab_Recycling_PickArea]![txtRecyclingTypeForReportHeader] OR (
[Forms]![frmPickForm_Estab_Recycling_PickArea]![txtRecyclingTypeForReportHeader] IS NULL AND ContainerType NOT IN ('paper','xyz','tuv')))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886