Syntax Problem: Refer to string not fieldname

[Guest]

Hi.

I have the following code running to generate an sql string from another
functions OnClick event (i'm using a querydef):

Function BuildSQLstring(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "tblIndividual.Forename,tblIndividual.Surname "
strFROM = "tblIndividual "

If ChkGender = -1 Then
strSELECT = strSELECT & ",tblIndividual.Gender "

strWHERE = "tblIndividual.Gender = " & "" & ComGender
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE

BuildSQLstring = True
RefreshDatabaseWindow

End Function


Everything works fine and dandy except for line 9: strWHERE =
"tblIndividual.Gender = " & "" & ComGender, which is where the problem lies.

When run the query is created with seemingly the correct sql (which looks
fine to me when the msgbox displays it), but instead of filtering on the
contents of ComGender (combobox) it seems to be taking the contents of
comGender and treating this string as a field name, and prompting me for a
parameter.

If I look at the design view of the newly created query, in the criteria for
the field "Gender" what is displayed is: " [F] " (for example), rather than
just " F " .


I'm assuming the error is to do with my syntax, but i've checked books and
this forum, but can't see what it is.

Thanks

 

[RoyVidar]

Hi.

I have the following code running to generate an sql string from
another functions OnClick event (i'm using a querydef):

Function BuildSQLstring(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "tblIndividual.Forename,tblIndividual.Surname "
strFROM = "tblIndividual "

If ChkGender = -1 Then
strSELECT = strSELECT & ",tblIndividual.Gender "

strWHERE = "tblIndividual.Gender = " & "" & ComGender
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE

BuildSQLstring = True
RefreshDatabaseWindow

End Function


Everything works fine and dandy except for line 9: strWHERE =
"tblIndividual.Gender = " & "" & ComGender, which is where the
problem lies.

When run the query is created with seemingly the correct sql (which
looks fine to me when the msgbox displays it), but instead of
filtering on the contents of ComGender (combobox) it seems to be
taking the contents of comGender and treating this string as a field
name, and prompting me for a parameter.

If I look at the design view of the newly created query, in the
criteria for the field "Gender" what is displayed is: " [F] " (for
example), rather than just " F " .


I'm assuming the error is to do with my syntax, but i've checked
books and this forum, but can't see what it is.

Thanks

When you use dynamic SQL, and pass criteria to text fields, you need
to tell that to the Jet engine, for instance by using text delimiters

strWHERE = "tblIndividual.Gender = '" & ComGender & "'"

 

[Guest]

Thanks Roy, exactly what I needed

Hi.

I have the following code running to generate an sql string from
another functions OnClick event (i'm using a querydef):

Function BuildSQLstring(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "tblIndividual.Forename,tblIndividual.Surname "
strFROM = "tblIndividual "

If ChkGender = -1 Then
strSELECT = strSELECT & ",tblIndividual.Gender "

strWHERE = "tblIndividual.Gender = " & "" & ComGender
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE

BuildSQLstring = True
RefreshDatabaseWindow

End Function


Everything works fine and dandy except for line 9: strWHERE =
"tblIndividual.Gender = " & "" & ComGender, which is where the
problem lies.

When run the query is created with seemingly the correct sql (which
looks fine to me when the msgbox displays it), but instead of
filtering on the contents of ComGender (combobox) it seems to be
taking the contents of comGender and treating this string as a field
name, and prompting me for a parameter.

If I look at the design view of the newly created query, in the
criteria for the field "Gender" what is displayed is: " [F] " (for
example), rather than just " F " .


I'm assuming the error is to do with my syntax, but i've checked
books and this forum, but can't see what it is.

Thanks

When you use dynamic SQL, and pass criteria to text fields, you need
to tell that to the Jet engine, for instance by using text delimiters

strWHERE = "tblIndividual.Gender = '" & ComGender & "'"