if null then this if not then this - combo box use 1

[Triacona]

Dear All,

Thank you for a great forum!

I have the following problem:
I have a query that produces different results via combo boxes and text boxes...

The user will either leave the combobox blank or fill it in.
One of these boxes are decision types.
So if the user chooses, PER (permitted) then the report displays all permitted applications.

However if the user wants all applications some with some without decisions
and they leave the box blank, when I run the query it outputs only applications with decisions instead of both with and without.

I tried the following:

Is Null Or Like "*" & [forms]![MainScreen].[cmbDcDec] & "*"

This worked if the combox box was blank;

However when the combobox was used for example PER:
The query would display the blank decisions and PER decisions at the same time;
whereas I only then want the PER decisions to display.

How would I craft the query to do this, considering the above criteria?

My only other thought is VB, please save me from that thought

Thank you for any forthcoming help.



Thank you,

Kind regards

Triacona

 

[Andrzejek]

Would [tt]IIF[/tt] work?
[tt]
IIF(Len([forms]![MainScreen].[cmbDcDec]) = 0, Is Null Or Like "*", Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Triacona]

Hi Andy,

Thanks for your help

I tried the following variations in the Criteria field in the query:

IIF(Len([forms]![MainScreen].[cmbDcDec]) = 0, Is Null Or Like "*", Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")

IIf(IsNull([forms]![MainScreen].[cmbDcDec]),Is Null Or Like "*",Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")

 Nz(Like "*" & [forms]![MainScreen].[cmbDcDec] & "*",Like "*")

None on of these work...
By that I mean:
I input a decision type into the combo box and the result shows no data
I leave the combo box blank and the result shows no data.

Is there anything I am doing wrong?

Thank you again for your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Hi Andy,

Another thing I noticed, is that it does not like the OR is null...

So I have also tried:

IIf(IsEmpty([forms]![MainScreen].[cmbDcDec]),Like "*",
Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")

IIf(IsNull([forms]![MainScreen].[cmbDcDec]),
Like "*",Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")

Thanks

Thank you,

Kind regards

Triacona

 

[dhookom]

You can't place operators inside the expressions. Set up the column to convert nulls to zero-length strings by appending ""

WHERE [Your Field Name] & "" Like "*" & [forms]![MainScreen].[cmbDcDec] & "*"

You can also use:

Like "*" & [forms]![MainScreen].[cmbDcDec] & "*" OR IsNull([forms]![MainScreen].[cmbDcDec])

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Duane,

Thank you for your help

Your second suggestion worked in the SQL view mode:

AND ((UNI7LIVE_DCAPPL.DECSN) [b]Like "*" & [forms]![MainScreen].[cmbDcDec] & "*")[/b]

The above code changed the query bulder view in the following way:

1. Repeated some of the criteria in the OR section of query builder
2. Added an expression:

IsNull([forms]![MainScreen].[cmbDcDec])​

Criteria for expression is in the OR section :

<> FALSE​

3. In the DECSN Criteria kept the Like "*" & [forms]![MainScree].[cmbDcDec] & "*"


The 1st suggestion did not work as desired...

I have run both scenarios and it works yay! Thank you!!
Only one thing it takes a while to run the query..?
Any way to streamline it?

Thank you again



Thank you,

Kind regards

Triacona

 

[dhookom]

Provide your full SQL view so we can assist with optimizing. If DECSN matches the exact value of the combo box then change the "LIKE" to "=".



Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,
Thanks for your help
I tried the

=

and the same amount of time transpired.

below is the SQL for the query:

[b]SELECT[/b] UNI7LIVE_DCAPPL.REFVAL, Onelinereplace([ADDRESS]) [b]AS[/b] Addr, UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.DCAPPTYP, 
DcAppTyp.CODETEXT AS ApplicationType, UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT [b]AS[/b] Decision, UNI7LIVE_CNWARD.WARDNAME, 
[b]IIf[/b]([DTYPNUMBCO] Between "0001" And "0006","Large Scale Major",
[b]IIf[/b]([DTYPNUMBCO] Between "0007" And "0012","Small Scale Major",
[b]IIf[/b]([DTYPNUMBCO] Between "0013" And "0018","Minor",
[b]IIf[/b]([DTYPNUMBCO] Between "0019" And "0027","Other Developments",
[b]IIf[/b]([DTYPNUMBCO] [b]In[/b] ("NREQ"),"NREQ",
[b]IIf[/b]([DTYPNUMBCO] [b]In[/b] ("TEL"),"Telecomunications",0)))))) [b]AS[/b] DevType, 
UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT
[b]FROM[/b] (((UNI7LIVE_DCAPPL LEFT JOIN DcOffCodeList ON UNI7LIVE_DCAPPL.OFFCODE = DcOffCodeList.OFFCODE) 
[b]LEFT JOIN[/b] UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) 
[b]LEFT JOIN[/b] DcDecisionCodes ON UNI7LIVE_DCAPPL.DECSN = DcDecisionCodes.CODEVALUE) 
[b]LEFT JOIN[/b] DcAppTyp ON UNI7LIVE_DCAPPL.DCAPPTYP = DcAppTyp.CODEVALUE
[b]GROUP BY[/b] UNI7LIVE_DCAPPL.REFVAL, Onelinereplace([ADDRESS]), UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.DCAPPTYP, 
DcAppTyp.CODETEXT, UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT, 
UNI7LIVE_CNWARD.WARDNAME, 
[b]IIf[/b]([DTYPNUMBCO] Between "0001" And "0006","Large Scale Major",
[b]IIf[/b]([DTYPNUMBCO] Between "0007" And "0012","Small Scale Major",
[b]IIf[/b]([DTYPNUMBCO] Between "0013" And "0018","Minor",IIf([DTYPNUMBCO] Between "0019" And "0027","Other Developments",
[b]IIf[/b]([DTYPNUMBCO] In ("NREQ"),"NREQ",
[b]IIf[/b]([DTYPNUMBCO] In ("TEL"),"Telecomunications",0)))))), 
UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.WARD, 
UNI7LIVE_DCAPPL.OFFCODE, DcOffCodeList.NAME, UNI7LIVE_DCAPPL.DATEWKLIST
[b]HAVING[/b] (((UNI7LIVE_DCAPPL.DCAPPTYP) Like "*" & forms!MainScreen!cmbDcAppTyp & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DATEAPVAL) Between Forms!MainScreen.txtStartDate And Forms!MainScreen.txtEndDate)
[b]And[/b] ((UNI7LIVE_DCAPPL.DECSN) Like "*" & forms!MainScreen.cmbDcDec & "*") 
[b]And[/b] ((UNI7LIVE_CNWARD.WARDNAME) Like "*" & forms!MainScreen.AddSrch & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DECTYPE) Is Null Or (UNI7LIVE_DCAPPL.DECTYPE) Like "*" & forms!MainScreen.SrchCrit & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DCSTAT) Like "*" & forms!MainScreen!CmbStat & "*")) 
[b]Or[/b] (((UNI7LIVE_DCAPPL.DCAPPTYP) Like "*" & forms!MainScreen!cmbDcAppTyp & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DATEAPVAL) Between Forms!MainScreen.txtStartDate And Forms!MainScreen.txtEndDate) 
[b]And[/b] ((UNI7LIVE_CNWARD.WARDNAME) Like "*" & forms!MainScreen.AddSrch & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DECTYPE) Is Null Or (UNI7LIVE_DCAPPL.DECTYPE) Like "*" & forms!MainScreen.SrchCrit & "*") 
[b]And[/b] ((UNI7LIVE_DCAPPL.DCSTAT) Like "*" & forms!MainScreen!CmbStat & "*") 
[b]And[/b] ((IsNull(forms!MainScreen.cmbDcDec))<>False))
[b]ORDER BY[/b] UNI7LIVE_DCAPPL.REFVAL;

Any further help is greatly appreciated
Thanks

Thank you,

Kind regards

Triacona

 

[dhookom]

My first question is why is this a totals query when there is no Sum(), Avg(), or Count()?
Next, I would move all of the "HAVING" clause into a "WHERE" clause since none of it filters against an aggregate.
Any "Like" should be changed to "=" if possible.
Every field in the WHERE clause should be indexed.
I would never hard code values into the SQL like you have done with all the nested IIF() statements. I would create a small DTYPNOMBCO lookup table with the min and max values and the "Large...", "Small..." etc.

This is the SQL without making too many changes that get rid of the HAVING clause:

SELECT UNI7LIVE_DCAPPL.REFVAL, Onelinereplace([ADDRESS]) AS Addr, UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.DCAPPTYP, 
DcAppTyp.CODETEXT AS ApplicationType, UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT AS Decision, UNI7LIVE_CNWARD.WARDNAME, 
IIf([DTYPNUMBCO] Between "0001" And "0006","Large Scale Major",
IIf([DTYPNUMBCO] Between "0007" And "0012","Small Scale Major",
IIf([DTYPNUMBCO] Between "0013" And "0018","Minor",
IIf([DTYPNUMBCO] Between "0019" And "0027","Other Developments",
IIf([DTYPNUMBCO] In ("NREQ"),"NREQ",
IIf([DTYPNUMBCO] In ("TEL"),"Telecomunications",0)))))) AS DevType, 
UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT

FROM (((UNI7LIVE_DCAPPL LEFT JOIN DcOffCodeList ON UNI7LIVE_DCAPPL.OFFCODE = DcOffCodeList.OFFCODE) 
LEFT JOIN UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) 
LEFT JOIN DcDecisionCodes ON UNI7LIVE_DCAPPL.DECSN = DcDecisionCodes.CODEVALUE) 
LEFT JOIN DcAppTyp ON UNI7LIVE_DCAPPL.DCAPPTYP = DcAppTyp.CODEVALUE

WHERE (((UNI7LIVE_DCAPPL.DCAPPTYP) Like "*" & forms!MainScreen!cmbDcAppTyp & "*") 
And ((UNI7LIVE_DCAPPL.DATEAPVAL) Between Forms!MainScreen.txtStartDate And Forms!MainScreen.txtEndDate)
And ((UNI7LIVE_DCAPPL.DECSN) Like "*" & forms!MainScreen.cmbDcDec & "*") 
And ((UNI7LIVE_CNWARD.WARDNAME) Like "*" & forms!MainScreen.AddSrch & "*") 
And ((UNI7LIVE_DCAPPL.DECTYPE) Is Null Or (UNI7LIVE_DCAPPL.DECTYPE) Like "*" & forms!MainScreen.SrchCrit & "*") 
And ((UNI7LIVE_DCAPPL.DCSTAT) Like "*" & forms!MainScreen!CmbStat & "*")) 
Or (((UNI7LIVE_DCAPPL.DCAPPTYP) Like "*" & forms!MainScreen!cmbDcAppTyp & "*") 
And ((UNI7LIVE_DCAPPL.DATEAPVAL) Between Forms!MainScreen.txtStartDate And Forms!MainScreen.txtEndDate) 
And ((UNI7LIVE_CNWARD.WARDNAME) Like "*" & forms!MainScreen.AddSrch & "*") 
And ((UNI7LIVE_DCAPPL.DECTYPE) Is Null Or (UNI7LIVE_DCAPPL.DECTYPE) Like "*" & forms!MainScreen.SrchCrit & "*") 
And ((UNI7LIVE_DCAPPL.DCSTAT) Like "*" & forms!MainScreen!CmbStat & "*") 
And ((IsNull(forms!MainScreen.cmbDcDec))<>False))

GROUP BY UNI7LIVE_DCAPPL.REFVAL, Onelinereplace([ADDRESS]), UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.DCAPPTYP, 
DcAppTyp.CODETEXT, UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT, 
UNI7LIVE_CNWARD.WARDNAME, 
IIf([DTYPNUMBCO] Between "0001" And "0006","Large Scale Major",
IIf([DTYPNUMBCO] Between "0007" And "0012","Small Scale Major",
IIf([DTYPNUMBCO] Between "0013" And "0018","Minor",IIf([DTYPNUMBCO] Between "0019" And "0027","Other Developments",
IIf([DTYPNUMBCO] In ("NREQ"),"NREQ",
IIf([DTYPNUMBCO] In ("TEL"),"Telecomunications",0)))))), 
UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.WARD, 
UNI7LIVE_DCAPPL.OFFCODE, DcOffCodeList.NAME, UNI7LIVE_DCAPPL.DATEWKLIST

ORDER BY UNI7LIVE_DCAPPL.REFVAL;

Duane
Hook'D on Access
MS Access MVP