IIF Function Issues (1 Viewer)

[Benjamin Bolduc]

Hello everyone,

I'm trying to create a criteria in the query of a report that is based off a text box in a form.

It works easily if I just put [Forms]![Prod Sched (BP)]![TheLine#] in the Line# column of my query builder.

My problem occurs when I don't want to specify a number and want to include them all. (1-4)

If I create an IIF statement as follows:

IIf(([Forms]![Prod Sched (BP)]![TheLine#])<>4,[Forms]![Prod Sched (BP)]![TheLine#]), <>5)

It should query all of the numbers, but it just returns no data. I've tried several different methods of expressing "All numbers", but it just wont work unless only one number is specified.

I'm hoping this makes sense and that someone can help me with this. Thanks!

-Ben

 

[KenHigg]

iif(Isnull([Forms]![Prod Sched (BP)]![TheLine#]),"*",[Forms]![Prod Sched (BP)]![TheLine#])

???
kh

 

[Benjamin Bolduc]

I tried that and the same thing keeps happening.

When I tell it to show everything through an expression, it shows nothing.

Now if I remove the expression from the criteria it does show everything so i know it's not my data.

Does anyone have any other thoughts?

Thanks!
-Ben

 

[Jon K]

It's a query Design View problem.


To avoid the problem, you can start a new column, combine the IIF expression with the field name and put them in the Field row, uncheck Show, and put True in the criteria row.

For example, if Line# can be any number between 1 and 4, you can use:-
---------------------------
Field: IIf([Forms]![Prod Sched (BP)]![TheLine#]<=4, [Line#]=[Forms]![Prod Sched (BP)]![TheLine#], True)

Show: uncheck

Criteria: True
---------------------------

So if either 1,2,3 or 4 is entered in the text box, records with that Line# will be returned. If the text box is left blank or the number entered is >4, all the records will be returned.
.

 

[Pat Hartman]

I would use:

Where [Forms]![Prod Sched (BP)]![TheLine#]) Is Null OR [Line #] = [Forms]![Prod Sched (BP)]![TheLine#]


BTW: Using special characters and/or embedded spaces for your object names is poor practice. Many RDBMS' won't allow them and neither will any language such as VB. It is really too bad that Access lets people form these bad habits.

 

[Benjamin Bolduc]

It works! Thank you for the fix guys.

I'll try to watch myself using special characters and spaces. I know someday it will come back to haunt me.

-Ben

 

[KenHigg]

It's a query Design View problem.

I'll be darn - Thxs

Ben, This may help:
RVBA Naming Conventions