Option Button to Restrict Qry

[Jason]

On my from I have an option button that limits the list in a combox to Active
employees. When it is not checked I would like the qry to return all records.
It works fine for when the option is true but not for when false (right now
it returns none). Can you see where I went wrong in the SQL? Also does anyone
know a way to refresh the data in the combobox without having to close and
relauch the form after the optioin button has been updated? Thank you

SELECT Tbl_EmployeeMaster.QI, Tbl_EmployeeMaster!LNAME & ", " &
Tbl_EmployeeMaster!FNAME AS FullName
FROM Tbl_EmployeeMaster
WHERE
(((Tbl_EmployeeMaster.Status)=IIf([Forms]![Frm_EmployeeSelect]![Option13]=True,"Active",Null)))
ORDER BY Tbl_EmployeeMaster!LNAME & ", " & Tbl_EmployeeMaster!FNAME;

 

[Dale Fye]

Jason,

Assuming that the only values in your [Status] field are Null and "Active"
then your SQL should look something like:

SELECT QI, LNAME & ", " & FNAME AS FullName
FROM Tbl_EmployeeMaster
WHERE [Status] = "Active"
OR ISNULL([Status]) <> [Forms]![Frm_EmployeeSelect]![Option13]
ORDER BY LNAME & ", " & FNAME;

Then, in the AfterUpdate event of your radio button, you just need to
requery the combo.

Private Sub rb_LimitToActive_AfterUpdate

me.cbo_FullName.Requery

End Sub

HTH
Dale

 

[Jason]

The values stored in the field are "Active", "Terminated", and "LOA" does
that make a diffence?

 

[Dale Fye]

Absolutely, it makes a difference. With those two values, ISNULL([Status])
will always return FALSE so you need to change the query to:

SELECT QI, LNAME & ", " & FNAME AS FullName
FROM Tbl_EmployeeMaster
WHERE [Status] = "Active"
OR ISNULL([Status]) = [Forms]![Frm_EmployeeSelect]![Option13]
ORDER BY LNAME & ", " & FNAME;

HTH
Dale

 

[Wei Lu [MSFT]]

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[tboyce]

Absolutely, it makes a difference. With those two values, ISNULL([Status])
will always return FALSE so you need to change the query to:

SELECT QI, LNAME & ", " & FNAME AS FullName
FROM Tbl_EmployeeMaster
WHERE [Status] = "Active"
OR ISNULL([Status]) = [Forms]![Frm_EmployeeSelect]![Option13]
ORDER BY LNAME & ", " & FNAME;

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

The values stored in the field are "Active", "Terminated", and "LOA" does
that make a diffence?
I have a form that uses an option group with four options numbered 1-4. I would like the query to return all numbered options, but when the query is run via a report i would like the report print the text option relevant rather than the numerical value. For example 1 = Auction, 2 = Return, 3 = General & 4 = PreAuction, so when the report runs it reads Auction, Return etc rather than 1, 2 or 3. Can you help me please. I am confused when it comes to updating the report and what I should be doing.

 

[tboyce]

I have a form that uses an option group with four options numbered 1-4. I
would like the query to return all numbered options, but when the query is
run via a report i would like the report print the text option relevant
rather than the numerical value. For example 1 = Auction, 2 = Return, 3 =
General & 4 = PreAuction, so when the report runs it reads Auction, Return
etc rather than 1, 2 or 3. Can you help me please. I am confused when it
comes to updating the report and what I should be doing.

 

[tboyce]