Using OR in Switch functions

[wayneh]

I have an option group on a form with three choices "Yes/No/All" I'm
using a switch function in a query to filter records on a Yes/No field.
There is no problem displaying records that are either "Yes" or "No"
but when I try to display "All", using (-1 Or 0) in the switch
function, I only get the "Yes" records (-1). This is driving me nuts as
I'm sure I had it working and don't know what I changed. The criteria
in the field is as follows:

Switch([Forms]![frm RepJobsDue]![JobLive]=1,-1,[Forms]![frm
RepJobsDue]![JobLive]=2,0,[Forms]![frm RepJobsDue]![JobLive]=3,(-1 Or
0))

I can't see what the problem is, as having (-1 or 0) by itself in as
the criteria will display all records.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

- -1 OR 0 is a binary OR not a logical OR. Any number binary OR'd with
zero will return that number. What you want to do is if JobLive = 3
then do nothing. Usually this situation calls for dynamically creating
the SQL string using VBA, 'cuz you are including/excluding a criteria
depending on user input. Something like this:
strSQL = "SELECT * FROM table_name "

If Me!JobLive = 1 Then
strWhere = "JobLive = True"
ElseIf Me!JobLive = 2 Then
strWhere = "JobLive = False"
Else
' do nothing, which will select ALL records
End If

If Len(strWhere)>0 then strSQL = strSQL & " WHERE " & strWhere

' do something w/ the SQL string.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcMvIoechKqOuFEgEQJeUQCeO4jaNDPZvbfJMWmHVn64JJ lHZ8IAoJHW
yEMChdxjQ4WkgkrdZBELEVWN
=d9/5
-----END PGP SIGNATURE-----
wa****@4taconic.com wrote:

I have an option group on a form with three choices "Yes/No/All" I'm
using a switch function in a query to filter records on a Yes/No field.
There is no problem displaying records that are either "Yes" or "No"
but when I try to display "All", using (-1 Or 0) in the switch
function, I only get the "Yes" records (-1). This is driving me nuts as
I'm sure I had it working and don't know what I changed. The criteria
in the field is as follows:

Switch([Forms]![frm RepJobsDue]![JobLive]=1,-1,[Forms]![frm
RepJobsDue]![JobLive]=2,0,[Forms]![frm RepJobsDue]![JobLive]=3,(-1 Or
0))

I can't see what the problem is, as having (-1 or 0) by itself in as
the criteria will display all records.