Less/greater than or equal to criteria question.

[McArthurGDM]

The only important fields here are "Period" and "CalcSort".

I want it so that if on a form someone chooses Feb that the CalcSort field will only show values 1 and 2. I tried putting in the code below, but for some reason I only get back the "CalcSort" value of 0. What am I doing wrong?

IIf([Forms]![Control]="Feb",[CalcSort]>=1 And [CalcSort]<=2,"")

MainID	OE	Years	Months	Dates	Net	Period	CalcSort
US CO.2015.Baseline	US CO	2015	February	2/1/2015	-0.56	Baseline	0
US CO.2015.Feb	US CO	2015	February	2/1/2015	3.24	Feb	2
US CO.2015.Jan	US CO	2015	February	2/1/2015	1.54	Jan	1

[ranman256]

you need the actual form name between control name and forms collective

IIf([Forms]![MyForm]![Control]="Feb",[CalcSort]>=1 And [CalcSort]<=2,"")

[amrut]

IIf([Forms]![Control]="Feb",[CalcSort]>=1 And [CalcSort]<=2,"")

I think you are missing the value if true part in iif statement.It can show 1 or 2, not both.
IIF(Condition,IF true this, If false this)
So, this is a valid statement,
IIf([Forms]![Control]="Feb",[CalcSort]=2,"")
If you want to sort the data you can use the date functions to get month number.

[McArthurGDM]

You are correct about the [MyForm]! part missing. I didn't paste it correctly. However, I still run into the issue of only 0 showing up.

[McArthurGDM]

Isn't there a way to set the IIF criteria to show multiple values if TRUE?

For instance:

IF Forms!MyForm!Control = "Feb" Then
      CalcSort <= 2 AND CalcSort >= 1
End If

or something like...

IF Forms!MyForm!Control = "Feb" Then
      1 <= CalcSort <=2
End If

I want multiple values to show up given that the condition holds true - not just one.

But for some reason, the way that I wrote it causes only 0 to show up... no idea why.

[ranman256]

Sorry , i misunderstood. What IS supposed to happen if you get 0?

[McArthurGDM]

I want the values 1 AND 2 to show up given that the criteria is true.

I always seem to have issues assigning variable values with IIF criteria statements.

That is, saying IIF(Forms!Form!Control! = "Feb", [CalcSort] <= 2 AND [CalcSort] >= 1, "")

[June7]

Is CalcSort a native field in table or a calculated field in query? In other words, are you trying to construct a field named [CalcSort] in query?

The last posted expression will return True or False or "" - but only if CalcSort is already a field in table or query.

What exactly do you want to use this expression for - as filter criteria in a query? Or to calculate value for CalcSort?

[McArthurGDM]

The CalcSort is a calculated field from a table.

That could be the root of my problem...

Say that I have:

Month	CalcSort
February	0
February	1
February	2
February	3
February	4
February	5
February	6
February	7
February	8

And I want the criteria

IIF ( Forms![MyForm]![Month] = "February", [CalcSort] <= 6 AND [CalcSort] >= 2, "" )

That is, if the month chosen on a form is February, then display records with CalcSort field values 2,3,4,5,6... otherwise nothing

But, the TRUE part of the statement seems to never work. even if I just say [CalcSort]>2, it dosen't work. It seems that an if statement can only take as a true value a simple criteria like a word or letter or something and not an expression as I have above.

[June7]

I don't use calculated fields in table. Presume Access will recognize CalcSort as a number type.

This is also why I don't use dynamic parameter queries.

SELECT * FROM tablename WHERE IIf([Forms![MyForm]![Month]="February" AND [CalcSort]>1 AND [CalcSort]<7, True, False)=True;

[McArthurGDM]

Nailed it. Thank you...

This actually showed me how to create multiple criteria expressions within an IIF statement.

Dynamic parameter queries are tricky...

How do you get around not using them?

[June7]

I build filter criteria in VBA and apply to form or report when opened or form Filter property for already open form.

Review: http://www.allenbrowne.com/ser-62.html