union query failing with yes no fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have a table with a series (20 ) off yes/no fields ( likely a mistake , but
here I am). Have a form allowing user to choose one or two of these fields
with a check ; this form is read by a query and a report is generated .
trouble is instead of getting logical AND's (which the query calls for)I
get logical Ors ; that Is I want only records with both "gym" and "adult"
checked.



This of course works:

SELECT *

FROM inquiry

WHERE ( (inquiry.adult)=forms!inq_query!adult)



And



( (inquiry.junior)=forms!inq_query!junior);





but when I added all 20 on each side of t he and, it failed , leading me to
try this union query , which to my surprise did not





SELECT *

FROM inquiry

WHERE

( (inquiry.adult)=(forms!inq_query!adult =-1))

or

( (inquiry.junior)=(forms!inq_query!junior=-1))



UNION select * from inquiry

WHERE







( (inquiry.adult)=(forms!inq_query!adult =-1))



or



( (inquiry.junior)=(forms!inq_query!junior=-1));



My first attempt was similar to the first but for 20 "or's" followed by an
"
AND" and then 20 more or's ; it fails for now obvious reasons .



substituting a "AND" for the union statement fails too .



Is there no other way but to programmatically read the page, pick out which
check box's are checked and build a query string similar to the first query
above ? Again , I have 20 possible choices ..
 
As you said, "likely a mistake". You can fix the core of the problem, or
suffer the never ending pain.
 
how better to have done it ? Write those checked into their own table
linked back to the main record ? I'm still unclear about why that union
didn't work. As of now I have the start of a working work around with :



Dim v1 As String '1st choice
Dim v2 As String '2nd choice
Dim vq As String 'query string
Dim i As String ' counter 1 or 2 checked
i = 0

If Me.adult.Value = -1 Then
v1 = "adult"
i = 1
End If

If Me.junior.Value = -1 Then
If i = 1 Then
i = 2
Debug.Print " adult and junior"
v2 = "junior"
Else
i = 1
v1 = "junior"
Debug.Print "junior only"
End If

End If

Etc for all .....


If i = 1 Then
vq = v1 + "=-1"
End If

If i = 2 Then
vq = v1 + "=-1" + " and " + v2 + " =-1"

End If

' vq = v1 + "=-1" + ";"
' vq = "'" + v1 + "=-1" + ";"
'vq = "'" + v1 + "'" + " =-1 " + ";"
'Debug.Print vq
'Stop
'Dim b As String
'b = "insert into inq_rep select * from inquiry where "
'b1 = b + vq
'Debug.Print b1
'Stop
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from inq_rep;"

DoCmd.RunSQL "insert into inq_rep select * from inquiry where " + vq
 
Back
Top