Radio Button Value In Query

[consjoe]

I have a form with an Option Group (frameBRDLate) setup that has 3 radio
buttons (Yes, No, Both). I then have a query that reads the value
returnedfrom frameBRDLate. The query pulls a "Yes/No" field from a table.
If -1 (Yes) is returned from the frame I want all "Yes" records returned
(this currently works correctly), if 0 (No) is returned from the frame I want
all "No" records returned (this currently works correctly). If 2 (both) is
returned from the frame I want all recrods (Yes and No) returned (this is not
working). When 2 is returned it ONLY returns me the "Yes" (-1) records. My
iif statement is below.

IIf([Forms]![frmReportSearch]![frameBRDLate]=2,([Assignment].[Late BRD])=0
Or ([Assignment].[Late BRD])=-1,[Forms]![frmReportSearch]![frameBRDLate])

I understand the whole Tripple state isse with an Option group but don't
believe that shoudl be an issue since I am using three different radio
buttons, not asking one radio button to give me three states.

If I add the below column to the end of my query I can see that 2 is in fact
being returned to my query.
Expr1: IIf([Forms]![frmReportSearch]![frameBRDLate]=2,"2 is returned","No")

This is driving me crazy, thanks in advance.

 

[Jeff Boyce]

If you open the form in design view and look at the properties of those
buttons, are you saying that the values of the buttons are -1, 0, 2?

Next, where are you putting that IIF() statement? If it's a query, please
post the SQL statement that includes it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve Schapel]

Consjoe,

If you are using this IIf function in the criteria of the query, I don't
think you can do that. It won't work. The query will use the value
returned by the IIf function, which is not what you want.

Also, you should realise that in Access, 0 means No/False, and *any other
integer* means Yes/True (-1 is the default, but that's as far as it goes).

Try it like this, which I *think* will work for you...
[Forms]![frmReportSearch]![frameBRDLate] Or
[Forms]![frmReportSearch]![frameBRDLate]=2

 

[consjoe]

Thanks to KenSheridan I have a solution that works.
However, I had to alter it a little.
In my module I had to put:
Public Function IsBRDLate(intOption As Integer, blnIsLate)
Select Case intOption
Case -1
IsBRDLate = -1
Case 0
IsBRDLate = 0
Case Else
IsBRDLate = blnIsLate
End Select
End Function

In my query I put:
IsBRDLate([Forms]![frmReportSearch]![frameBRDLate],[Assignment].[Late BRD])
in the criteria section of the Late BRD field.

Thanks again

If you wrap it in a function you can handle all three possibilities:

Public Function IsBRDLate(intOption As Integer, blnIsLate As Boolean) As
Boolean

Select Case intOption
Case -1
IsBRDLate = blnIsLate
Case 0
IsBRDLate = Not blnIsLate
Case Else
IsBRDLate = True
End Select

End Function

Paste the above function into any standard module in the database. If it’s a
new module save it under a different name from the function, e.g. mdlBRDStuff.


Then in the query you'd pass the value of the option group and the value of
the Boolean (Yes/No) column in the table into the function in the query's
WHERE clause:

WHERE IsBRDLate([Forms]![frmReportSearch]![frameBRDLate], [Assignment].[Late
BRD])

In query design view you'd put:

IsBRDLate([Forms]![frmReportSearch]![frameBRDLate], [Assignment].[Late BRD])

in the 'field' row of a blank column in the query design grid (all as one
line; it may be split over two above in your newsreader), uncheck the 'show'
check box, and put:

True

in the same column's 'criteria' row.

Ken Sheridan
Stafford, England

I have a form with an Option Group (frameBRDLate) setup that has 3 radio
buttons (Yes, No, Both). I then have a query that reads the value
returnedfrom frameBRDLate. The query pulls a "Yes/No" field from a table.
If -1 (Yes) is returned from the frame I want all "Yes" records returned
(this currently works correctly), if 0 (No) is returned from the frame I want
all "No" records returned (this currently works correctly). If 2 (both) is
returned from the frame I want all recrods (Yes and No) returned (this is not
working). When 2 is returned it ONLY returns me the "Yes" (-1) records. My
iif statement is below.

IIf([Forms]![frmReportSearch]![frameBRDLate]=2,([Assignment].[Late BRD])=0
Or ([Assignment].[Late BRD])=-1,[Forms]![frmReportSearch]![frameBRDLate])

I understand the whole Tripple state isse with an Option group but don't
believe that shoudl be an issue since I am using three different radio
buttons, not asking one radio button to give me three states.

If I add the below column to the end of my query I can see that 2 is in fact
being returned to my query.
Expr1: IIf([Forms]![frmReportSearch]![frameBRDLate]=2,"2 is returned","No")

This is driving me crazy, thanks in advance.