having problem at "qdf.SQL = strSQL"

[Philipke]

Hello,

I made a database for managing people at my company. I'm trying to make some sort of search page in which people first select a competency (=query) and than a speciality (= Yes/No column in that query) with comboboxes. The goal is to get a feedback with all people that have a yes in the column. I am pretty new to the whole access/VB/SQL thing, so I have put something together based on what I find on the web (I'm pretty shure I've probably made some mistakes). I have now this code:

Private Sub Combo10_Change()

Dim db As DAO.database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strQuery As String
Dim strQueryColumn As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySearchSolution")
strQuery = "qry" & Me.Combo8.Value
strQueryColumn = strQuery & ".[" & Me.Combo10.Value & "]"

strSQL = "SELECT '" & strQueryColumn & "'" & _
"FROM '" & strQuery & "' " & _
"WHERE '" & strQueryColumn & "'= '1' ;"
qdf.SQL = strSQL
DoCmd.OpenQuery "qrySearchSolution"
Set qdf = Nothing
Set db = Nothing

End Sub

Combo10 is the second combobox. When I run this, I get an error 3450 Incomplete Query Clause. The debugger stops at qdf.SQL = strSQL and when I hover over the qdf.SQL part it says " qdf.SQL= "SELECT 'aValue';" with at the end two small squares. So the qdf.SQL part does not get the strSQL value. Is there someone among you that can help a novice like me out or suggest an other (easier) way to reach my goal?

Best Regards,
Philip

 

[PWise]

try

strQueryColumn = "[" & Me.Combo10.Value & "]"

strSQL = "SELECT " & strQueryColumn & " " & _
           " FROM " & strQuery & " " & _
          "WHERE " & strQueryColumn & "= -1  ;"

note the -1 for true

 

[Andrzejek]

Also try this:

strSQL = "SELECT '" & strQueryColumn & "'" & _
           "FROM '" & strQuery & "' " & _
          "WHERE '" & strQueryColumn & "'= '1'  ;"[blue]
Debug.Print strSQL[/blue]
 qdf.SQL = strSQL

Debug.Print will display your SQL in Immediate Window in your VBA editor. You will see what you send to your data base, the SQL statement that is.

Have fun.

---- Andy

 

[Philipke]

Thank you very much! It worked like a charm! I only spend a day looking for that solution you guys rock!