Query By Form SQL String (Again)

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

stevecrowhurst

I have redone the code and the function is now written
like this:

Function QBFTest1(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT tblConsignment.ConsignID,
tblConsignment.Carrier, HRBaseTbl.FirstName,
HRBaseTbl.Surname "



strFROM = "FROM HRBaseTbl INNER JOIN tblConsignment ON
HRBaseTbl.HRID = tblConsignment.HRID "


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



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

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

QBFTest1 = True


End Function


If I debug this function I get:

SELECT tblConsignment.ConsignID, tblConsignment.Carrier,
HRBaseTbl.FirstName, HRBaseTbl.Surname FROM HRBaseTbl
INNER JOIN tblConsignment ON HRBaseTbl.HRID =
tblConsignment.HRID WHERE tblConsignment.HRID =


Running the function produces runtime error 3075

Again thank you for your time and trouble.
 
[email protected] said:
I have redone the code and the function is now written
like this:

Function QBFTest1(ByRef strSQL As String) As Boolean

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

strSELECT = "SELECT tblConsignment.ConsignID,
tblConsignment.Carrier, HRBaseTbl.FirstName,
HRBaseTbl.Surname "



strFROM = "FROM HRBaseTbl INNER JOIN tblConsignment ON
HRBaseTbl.HRID = tblConsignment.HRID "


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



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

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

QBFTest1 = True


End Function


If I debug this function I get:

SELECT tblConsignment.ConsignID, tblConsignment.Carrier,
HRBaseTbl.FirstName, HRBaseTbl.Surname FROM HRBaseTbl
INNER JOIN tblConsignment ON HRBaseTbl.HRID =
tblConsignment.HRID WHERE tblConsignment.HRID =


Running the function produces runtime error 3075

Again thank you for your time and trouble.
Click to expand...

It's better to post followups in the same discussion thread, Steve.

Where is this code located? It would have to be in the class module of
the form containing the controls you refer to -- it could not be in a
standard module. You can plainly see that no value is being picked up
from cboHRID. Either that control's value is Null or a zero-length
string, or the name isn't being recognized as a control reference at all
but the module doesn't have Option Explicit turned on. What do you see
when, while stepping through the code, you let your mouse pointer hover
over the names cboHRID, ChkCarrier, and cboCarrier?
 
I have redone the code and the function is now written
like this:

Function QBFTest1(ByRef strSQL As String) As Boolean
Click to expand...
[snip]

Please don't post the same question multiple times in the
same day. It just clutters up the newsgroup and, if it gets
any answers, they may be spread across the multiple threads
making it difficult for anyone to figure out what ground has
already been covered.

For this question look at your earlier thread where Dirk
posted a lengthy and thorough response.
 
Back
Top