Problem using option buttons for query

[ITguy1981]

Once again I make progress and run in to an issue. Currently, I have a report that pulls data from a query. The query uses this criteria: EMPLOYEE FIELD, criteria Like "*" & [Leave blank or enter part or all employees name] & "*", WORK DATE FIELD, criteria Between [Enter Beginning Date:] And [Enter Ending Date:], STATE FIELD, criteria Like "*" & [Enter WV, PA, or leave blank for both] & "*", and HOURS FIELD with no criteria. The report pulls the data fine. I now have a form to enter the data in which the query pulls the EMPLOYEE from a text box, the beginning and ending dates for WORK DATE from date pickers and it works great. I tried adding radio buttons or option buttons for my STATE field choice. The 3 buttons on the form are PA, WV, and Both. I can't seem to figure out how put that in to a query so it can recognize which button is selected to return PA, WV, Both in the query. I guess the Both option in the query would actually be blank since no entry for STATE returns both by default. Hopfeully, using option buttons in a form and then using the entry in the form in a query is a lot easier for you.
/beating head of desk. lol

 

[ITguy1981]

ok so I was going about the procedure right, but I most of had some wrong syntax or something, but I still have one small flaw. I have success with my state field criteria as IIf([Forms]![Payroll Report Form]![Frame28]=1,"PA",IIf([Forms]![Payroll Report Form]![Frame28]=2,"WV","")). The last part I believe is wrong. The form works selecting WV or PA from the option menu, but when I select both it doesn't work. I just realized I have to have an option 3 value in the criteria. Is there a better syntax to use like an OR statement?

 

[Taruz]

Hi..

except for the first two options to list all records, can write the field name of the criteria..:


IIf([Forms]![Payroll Report Form]![Frame28]=1,"PA",IIf([Forms]![Payroll Report Form]![Frame28]=2,"WV",[STATE]))

I hope, I understood the problem correctly.

 

[ITguy1981]

I managed to get the form to fully populate my query criteria. Here is what I used
IIf([Forms]![Payroll Report Form]![Frame28]=1,"PA",IIf([Forms]![Payroll Report Form]![Frame28]=2,"WV",IIf([Forms]![Payroll Report Form]![Frame28]=3,([Daily Time].[State]),""))). The IIF function probably wasn't the best way to go, but it works. I just got your email reply. I'm going to go back and take the 3rd statement out and just add the ([Daily Time].[State]) to the false of the second statement. Thanks a bunch. My next task is going to be to fix my report. Currently, I have a report with pretty much the same type of query as the control, but it asks for the information by dialog boxes instead of having an input form. I'm going to see if I can swap those out, but I think i'll have the issue of opening the report, the report calling the query and the query not having data because it is called from the form. Any thoughts on that if that problem if that makes sense?

 

[KathCobb]

I know this is an old thread, but it helped me and I wanted to save it to my discussions and commenting on it was the only way I knew how

 

[isladogs]

Hi Kath
In thread tools, you could have clicked subscribe to this thread.
You can retrieve subscribed threads from the quick links menu