Query By Form SQL string

  • Thread starter Thread starter stevecrowhurst
  • Start date Start date
S

stevecrowhurst

Could you guys please cast an eye over my code and give
me a few tips. Thank you for taking the time & trouble to
assist a "newbie"
My problems begin at strFROM could you please help, the
code only produces the following SQL string:

SELECT c.*
FROM tblConsignment AS c;

Some tips and examples of creating would be great, again
thank you all for reading this electronic missive.




Function QBFTest(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT c.* "

'set an alias "c" for tblConsignment and "h" for HRBaseTbl

strFROM = "FROM tblConsignment AS c "
If chkHRID Then
strFROM = strFROM & " INNER JOIN HRBaseTbl h " & _
"ON c.HRID = h.HRID "


'check for second or more WHERE term
If strWHERE <> """" Then strWHERE = strWHERE & " AND "
strWHERE = h.HRID = " & cboHRID & _"
End If

If ChkCarrier Then
'check for second or more WHERE term
If strWHERE <> " " Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "c.ConsignID = " & cboCarrier
End If

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

QBFTest = True


End Function
 
[email protected] said:
Could you guys please cast an eye over my code and give
me a few tips. Thank you for taking the time & trouble to
assist a "newbie"
My problems begin at strFROM could you please help, the
code only produces the following SQL string:

SELECT c.*
FROM tblConsignment AS c;

Some tips and examples of creating would be great, again
thank you all for reading this electronic missive.




Function QBFTest(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT c.* "

'set an alias "c" for tblConsignment and "h" for HRBaseTbl

strFROM = "FROM tblConsignment AS c "
If chkHRID Then
strFROM = strFROM & " INNER JOIN HRBaseTbl h " & _
"ON c.HRID = h.HRID "


'check for second or more WHERE term
If strWHERE <> """" Then strWHERE = strWHERE & " AND "
strWHERE = h.HRID = " & cboHRID & _"
End If

If ChkCarrier Then
'check for second or more WHERE term
If strWHERE <> " " Then strWHERE = strWHERE & " AND "
strWHERE = strWHERE & "c.ConsignID = " & cboCarrier
End If

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

QBFTest = True


End Function
Click to expand...

Certain lines look wrong to me, but there's got to be something missing,
because the result string you posted has a terminating semicolon, and
the code you posted never adds one.

This looks wrong:
If strWHERE <> """" Then strWHERE = strWHERE & " AND "
strWHERE = h.HRID = " & cboHRID & _"
Click to expand...

strWhere is never (AFAICT) going to be equal to a quote character, and
there appear to be mismatched quotes in your assignment statement. I
think maybe you wanted this:

If strWHERE <> "" Then strWHERE = strWHERE & " AND "
strWHERE = strWhere & "h.HRID = " & cboHRID

This also looks wrong:
If strWHERE <> " " Then strWHERE = strWHERE & " AND "
Click to expand...

strWhere is also not going to be equal to a single space (" "), if
nothing's been assigned to it yet. Rather, it will be a zero-length
string (""). So the line should be

If strWHERE <> "" Then strWHERE = strWHERE & " AND "

However, I don't see how these mistakes can be causing strWhere to
remain empty, unless ChkCarrier is False. By the way, I recommend that
you protect yourself by writing the test as one of the following:

If (ChkCarrier) Then
If ChkCarrier = True Then
If ChkCarrier.Value Then

The sensible thing to do to debug a problem like this is to set a
breakpoint in the code and step through it, observing the flow of
control and examining the values of relevant variables as you go along.
 
Back
Top