OBF Build SQL String for function

[stevecrowhurst]

Could you guys run your eye over the function below and give me a few tips on the WHERE clause.
I'm trying to construct a QBF and I'm fighting a very stringent deadline. Thanks for the help.

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 <> &quot; &quot; Then strWHERE = strWHERE & &quot; AND &quot;
strWHERE = strWHERE & &quot;h.HRID = &quot; & cboFirstNameID
End If

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

strSQL = strSELECT & strFROM
If strWHERE <> &quot; &quot; Then strSQL = strSQL & &quot;WHERE &quot; & strWHERE

BuildSQLString = True
End Function

 

[Beejay57]

Not Really strong in sql but
i think when building the sql you will need to use something
like this
strWHERE = strWHERE & &quot;((h.HRID) = &quot; & cboSurnameID & &quot&quot;

and also when you have finished adding all your &quot;And&quot; then
you will need a extra &quot;&quot; at the end of the string

 

[dhookom]

Have you tried debugging by using lines like
Debug.Print strSQL
or
MsgBox strSQL

For a complete solution that you can drop into any mdb, check the DH Query By Form at

http://www.rogersaccesslibrary.com/OtherLibraries.asp.

Duane
MS Access MVP