Use a between statement embedded in an iif statement

[klinsman08]

I have 3 radio buttons belonging to a frame on a query form. Based on which radio button is selected, I want to change the query criteria.

The radio buttons have to do with which year to search for in a table. The first radio button is "Any," so all records in the table should be returned. The second radio button is "A single year," and if the user selects that radio button and enters a specific year in the corresponding box, all records containing that year should be returned. The third radio button (and the one I'm having trouble with) is "A range of years." In this one, a user enters 2 years, and the query should return all records within those two years. I have been trying to use a BETWEEN statement for this; it works by itself, but when I embed it in an iif statement (ie if the 1st radio button is selected do X, if the 2nd radio button is selected to Y, and if the 3rd radio button is selected do Z), it won't return any results.

The SQL code:

SELECT tblMaster.*</SPAN></SPAN>
FROM tblMaster</SPAN></SPAN>
WHERE (((tblMaster.Yr)=</SPAN></SPAN>

IIf(</SPAN></SPAN>
[Forms]![frmQueryMatchingJobs]![Frame48]=1,</SPAN></SPAN>
[tblMaster]![Yr],</SPAN></SPAN>

IIf([Forms]![frmQueryMatchingJobs]![Frame48]=2,</SPAN></SPAN>
[Forms]![frmQueryMatchingJobs]![txtYearFixed],</SPAN></SPAN>

IIf([Forms]![frmQueryMatchingJobs]![Frame48]=3,</SPAN></SPAN>
[tblMaster].[Yr] Between [Forms]![frmQueryMatchingJobs]![txtYearLB] And [Forms]![frmQueryMatchingJobs]![txtYearUB],</SPAN></SPAN>
[tblMaster]![Yr]</SPAN></SPAN>



)</SPAN></SPAN>
)</SPAN></SPAN>
)</SPAN></SPAN>
)</SPAN></SPAN>
);</SPAN></SPAN>

 

[Weaver]

Have you looked at using the case statement in SQL? Not 100% if this works in Access (I'm assuming you're using Access) but it's probably worth a try.

On the other hand, maybe you could just build a string based on the radio buttons rather than getting the SQL to run directly from the values and then execute that instead?

 

[klinsman08]

Yes, I am using Access, however I am very new at it. I've done a lot of programming in Excel, so I know VBA but not really SQL.

This is how I tried to use a SELECT-CASE statement; it gets an error when I try to run it.
SELECT tblMaster.*
FROM tblMaster
SELECT CASE [Forms]![frmQueryMatchingJobs]![Frame48]
CASE 1
tblMaster.Yr=[tblMaster]![Yr]
END SELECT

Re your string suggestion - where would this go? SQL window, Design Mode, or behind the scenes in VBA?