Building SQL String from QBF form.

[Steve]

Could you "guys" run your eyes of the following two
snippets of code and explain where I've gone awry, I
attempting to adapt an example from a book. I want to
create a QBF form, build an SQL string and display the
product of the function in a MSG box. The string is built
as far as the WHERE clause and then error message 3145
(syntax error in the WHERE clause) is displayed. If
possible could anybody give me a basic example and I
could take it from there. Thank you for your time.

Function BuildSQLString(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT
s.*.*" 'set the columns to
return results from
'(all columns
in this case)
'set an alias "h" for HRBaseTbl
strFROM = "FROM HRBaseTbl As h "

If chkFirstNameID Then
'check for 2nd or more WHERE term
If strWHERE <> " " Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "h.HRID = " & cboFirstNameID
End If

If chkSurnameID Then
'check for 2nd or more WHERE term
If strWHERE <> " " Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "h.HRID = " & cboSurnameID
End If

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

BuildSQLString = True
End Function


Private Sub cmdFind_Click()

Dim strSQL As String
Dim strTableName As String



If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

MsgBox strSQL

CurrentDb.QueryDefs("qryExample").SQL = strSQL


End Sub

 

[John Spencer (MVP)]

Simplest method to trouble shoot would be to add a Debug.print statement to your
code immediately after you finish building the strSQL, followed by a Stop
statement. Then copy and paste the SQL from the immediate pane into an query
and see what is wrong there. Go back and fix your code and then continue until
you get it right. Many comments posted in line

Function BuildSQLString(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT s.*.*" <--- "SELECT H.*"
<---s.*.* makes no sense to me

'set an alias "h" for HRBaseTbl
strFROM = "FROM HRBaseTbl As h " <--- Should this be H or S

Easy fix for check for AND is not to bother. Just set strWHERE as follows.

StrWhere = "TRUE = TRUE"

Otherwise check the length of strWhere vice checking to see if it is one space
long. Unless you set it to one space, then it will be zero spaces.

If chkFirstNameID Then
'check for 2nd or more WHERE term
strWHERE = strWHERE & " AND h.HRID = " & cboFirstNameID
End If

If chkSurnameID Then
'check for 2nd or more WHERE term
strWHERE = strWHERE & " AND h.HRID = " & cboSurnameID
'NOTE that this sets the same field to a different value, so you will probably
never have a match.
End If


<---ADD a Space between the SELECT clause and the FROM clause
<---ALSO add a space between the FROM clause and the WHERE clause

strSQL = strSELECT & " " & strFROM & " WHERE " & strWHERE

BuildSQLString = True

 

[Marshall Barton]

Could you "guys" run your eyes of the following two
snippets of code and explain where I've gone awry, I
attempting to adapt an example from a book. I want to
create a QBF form, build an SQL string and display the
product of the function in a MSG box. The string is built
as far as the WHERE clause and then error message 3145
(syntax error in the WHERE clause) is displayed. If
possible could anybody give me a basic example and I
could take it from there. Thank you for your time.

Function BuildSQLString(ByRef strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "SELECT s.*.*" 'set the columns to return
'results from (all columns in this case)

Where did the table s come from?? What is the *.* supposed
to be? Try this:

strSELECT = "SELECT h.* "

Note the space at the end that separates the above from the
FROM clause below.

'set an alias "h" for HRBaseTbl
strFROM = "FROM HRBaseTbl As h "

If chkFirstNameID Then

You don't need to check for existing Where string. You can
clean it up once after it is complete. Besides, you were
checking for a blank character when you should have been
checking for a zero length string ("")

strWHERE = strWHERE & " AND h.HRID=" & cboFirstNameID

End If

If chkSurnameID Then

strWHERE = strWHERE & " AND h.HRID = " & cboSurnameID

Something looks funny here, HRID can not match two different
things.

End If

strSQL = strSELECT & strFROM

Now clean up the Where clause and add it to the SQL
statement:
strSQL = strSQL & "WHERE " & Mid(strWHERE, 6)