IIF Statement with a ComboBox

[Canadianskibum]

I am trying to create an IFF statement based on the value of a ComboBox. I have named the ComboBox [ReturnLength] and I have set the values when I setup the ComboBox. The problem is it is not reading the statement as true and is kicking back a false result. I was hoping someone can take a look and let me know what I am doing wrong.

Like IIf([Forms]![Screener Form]![ReturnLength]="3 Month",Between [Forms]![Screener Form]![LowerReturn] And [Forms]![Screener Form]![HigherReturn],"*").

Access keeps changing the expression to:


Like IIf([Forms]![Screener Form]![ReturnLength]="3 Month",([Approved Advisory List - SAI].[Total Ret 3 Mo]) Between [Forms]![Screener Form]![LowerReturn] And [Forms]![Screener Form]![HigherReturn],"*")

Should I build something in VB?

[pbaldy]

Are there other values for the combo? If it's that or all, I'd make the combo Null for all and use this:

http://theaccessweb.com/queries/qry0001.htm

If there are other options, I'd just have the query point to the form values and populate them from the combo with appropriate values.

[Canadianskibum]

Thank you for responding pbaldy.

I have viewed that response and I am a still a bit unsure what to do. I have a combobox that has values, "3 months", "1 Year", "3 Year", Etc.. and I have colums with those titles that I would like the query to have a pramater set to depending which value in the combobox is set to. For example if someone selects "3 months" and buts and enters 1 to 5 the query with supply all the records that have a 3 month containing those values. I have the between parameter functioning properly but I can not get the IIF statement to work.

Thanks for you help.

[ranman256]

the 'like' goes in the query (that is read by the combo)

id have like pbaldy says, user picks the time frame,
the code adjusts the text boxes on the form: txtStartDate, txtEndDate

and the query pulls from that.

[Canadianskibum]

Ranman,

Are you saying have that once the user picks a value in the combobox then new textboxs "appear" that are attached to the value in the combobox?

[pbaldy]

It would seem with choices like that you always want the textboxes used, just with different dates in them. I'd have code behind the combo that set them, like this for the 3 month selection:

Me.FromTextbox = DateAdd("m", -3, Date())
Me.ToTextbox = Date()

[Canadianskibum]

PBaldy, there are no dates in the text box, basically I am trying to build a Mutual Fund Screener. In the table I have a list of mutual funds and I have columns with the the returns for 3 months, 1 year, 3 Year, 5 Year and all the mutual funds have different values in these columns. When the user selects "1 year" and then type 2.5%-5% I want the query to filter all results besides the records that have 2.5%-5% returns in the "1 Year" column and null the other return values columns.

I hope this explains it a bit more.

[pbaldy]

Ah sorry, I misunderstood. I would still do it a different way. What's the end goal here? Filtering the open form, opening a report, etc.

[Canadianskibum]

The goal is to run a query based on the information, here is a screen shot of the form:



So once the user adjusts the categories and click view results a query appears with the information.

[pbaldy]

Well, presumably the query is the source of a form or report (most of us never let users in tables or queries directly). In that case I'd combine this:

http://www.baldyweb.com/wherecondition.htm

with selecting the field, like (again for the 3 month selection):

DoCmd.OpenReport "ReportName", acViewPreview, , "[Total Ret 3 Mo] Between " & [Forms]![Screener Form]![LowerReturn] & " And " & [Forms]![Screener Form]![HigherReturn]

If you're actually opening a query, I think using a criteria like you're trying to will get really complicated. I'd probably change the SQL of it with a QueryDef. But then I wouldn't open a query at all.

By the way, spaces and symbols in your object names are not worth the bother in the long run.

[Canadianskibum]

Pbaldy,

Thank you for your help and suggestions, I will give this a try today!!!

Thanks,

[pbaldy]

No problem, post back if you get stuck.