Building SQL String For Use In A Function

[Steve]

Could any of you "kind and generous guys" explain what
I've done wrong, I get error messages indicating that the
problem lies within the WHERE clause. I'm trying to adapt
an example from a book. I'm using Access 2002 and DAO.
Any help would be appreciated.

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

 

[Douglas J. Steele]

If strWHERE hasn't been initialized, it contains a zero-length string (""),
not a space (" ")

Your first check is going to put " AND ", since strWHERE is not equal to "
". Try using If Len(strWHERE) > 0 (while it may seem counterintuitive to use
a function, it's actually more efficient than comparing to "")

 

[Dirk Goldgar]

Could any of you "kind and generous guys" explain what
I've done wrong, I get error messages indicating that the
problem lies within the WHERE clause. I'm trying to adapt
an example from a book. I'm using Access 2002 and DAO.
Any help would be appreciated.

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

You'd better post the rest of it. I see nothing obviously wrong in what
you've posted so far, but there's plenty of room for error in how you
assemble the parts to complete the SQL string. Also, it might help to
put a breakpoint before the function returns and examine the value of
strSQL (which I assume is where the completed SQL statement gets put).

 

[Tim Ferguson]

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

I'm not quite sure what s.*.* means, although it may work in your
environment. The normal phrase here is SELECT h.*

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

I note there is no space at the beginning of this string, nor at the end of
strSELECT, so you might run into problems if you are intending simply to
concatenate them.

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

As long as HRID is a numeric, this is okay. If it is a text value, though,
you will need to delimit it properly with """". What happens if the cbo is
empty (i.e. null)?

End If

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

Same as above

End If

There is not a lot here that should cause problems, with the provisos
mentioned. The best cure for building SQL strings is to put a MsgBox or
Debug.Print strSQL to see exactly what is being sent to the db engine.

PS It is great to see someone putting 'comment text in posted code. Thank
you.


Hope that helps


Tim F