SQL/VB code to debug

[hobbes]

Hi,

I have been working with this code for several days and can't figure out where I'm going wrong. I am new to SQL and VB, but not to Access. Does anyone see where I'm going wrong in my code? This is behind a form with six combo boxes and 48 check boxes. Ideally, I would like the user to select whatever criteria they are looking for in the combo boxes (if it's left empty, I would like a query to pull everything), and select the checkbox next to the field(s) they would like displayed (this way not every field is pulled, only the ones they need). So basically, I'm asking them to create their own query with the form I'm creating. Now, back to my code. When I try to run the report from the form, the debugger highlights qdfSQL = strSQL. I'm not entirely sure what's going on or what I'm doing wrong, but here is my code:

Private Sub cmdRunReport_Click()


Dim strSelect As String
Dim strSender As String
Dim strInvestOfficer As String
Dim strCUSIP As String
Dim strSenderRef As String
Dim strTradeRef As String
Dim Ctl As Control
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb


If Not QueryExists("qrySWIFTSelection") Then
Set qdf = db.CreateQueryDef("qrySWIFTSelection")
Else
Set qdf = db.QueryDefs("qrySWIFTSelection")
End If

If IsNull(Me.cboSender.Value) Then
strSender = " Like '*' "
Else
strSender = Me.cboSender.Value
End If

If IsNull(Me.cboInvestOfficer.Value) Then
strInvestOfficer = " Like '*' "
Else
strInvestOfficer = Me.cboInvestOfficer.Value
End If

If IsNull(Me.cboCUSIP.Value) Then
strCUSIP = " Like '*' "
Else
strCUSIP = Me.cboCUSIP.Value
End If

If IsNull(Me.cboAccount.Value) Then
strAccount = " Like '*' "
Else
strAccount = Me.cboAccount.Value
End If

If IsNull(Me.cboSenderRef.Value) Then
strSenderRef = " Like '*' "
Else
strSenderRef = Me.cboSenderRef.Value
End If

If IsNull(Me.cboTradeRef.Value) Then
strTradeRef = " Like '*' "
Else
strTradeRef = Me.cboTradeRef.Value
End If

strSQL = "SELECT tblSWIFT.* " & _
" FROM [tblSWIFT] " & _
" WHERE tblSWIFT.[SENDER NAME] " & strSender & _
" AND tblSWIFT.[INVEST OFFICER NAME] = " & strInvestOfficer & _
" AND tblSWIFT.[CUSIP NUMBER] = " & strCUSIP & _
" AND tblSWIFT.[TRUST ACCOUNT] = " & strAccount & _
" AND tblSWIFT.[SENDER REF] = " & strSenderRef & _
" AND tblSWIFT.[TRADE REFERENCE] = " & strTradeRef & ";"

qdf.SQL = strSQL

For Each Ctl In Me
Select Case Ctl.ControlType
Case Is = acCheckBox
If Ctl = -1 Then
strSelect = strSelect & ", " & Ctl.Tag
End If
End Select

Next
Set Ctl = Nothing
strSelect = Right(strSelect, Len(strSelect) - 2)

DoCmd.OpenQuery ("qrySWIFTSelection")


End Sub

 

[KenHigg]

With out having gone through your code with a fine tooth comb, I would suggest you examine the strSQL var in the immediate window...

kh

 

[jgc31]

I think you are missing brackets and quotation marks in the WHERE definition examples as follows

SELECT TblIssuetypes.[Issue number]
FROM TblIssuetypes
WHERE (((TblIssuetypes.[Issue number])="9"));


SQLstatement = "SELECT TblIssuetypes.[Issue name], TblIssuetypes.[Issue number] FROM TblIssuetypes WHERE (((TblIssuetypes.[Issue number])= """ & IndividIssue & """" & "));"

 

[Mile-O]

This is behind a form with six combo boxes and 48 check boxes. Ideally, I would like the user to select whatever criteria they are looking for in the combo boxes (if it's left empty, I would like a query to pull everything), and select the checkbox next to the field(s) they would like displayed (this way not every field is pulled, only the ones they need).

I'd ditch all those checkboxes and get a Mulit-Select listbox - much easier to maintain. If you add fields later to the table you'll need to find space for them on the form, too. With a Multi-Select you can do a loop to add all the field names to a delimited string and use it as the listbox's RowSource (ValueList). Thus, when it comes to building a QueryDef, you only need to loop through the listbox's ItemsSelected property to get the fields you want for your query.

With respect to your code, be explicit when working with data objects. i.e. DAO.Database, DAO.Recordset, DAO.QueryDef

Regarding checking these combos:

You have:

If IsNull(Me.cboInvestOfficer.Value) Then
strInvestOfficer = " Like '*' "
Else
strInvestOfficer = Me.cboInvestOfficer.Value
End If

This does the same thing and is less problematic.

If Not IsNull(Me.cboInvestOfficer) Then
    strInvestOfficer = Me.cboInvestOfficer
End If

I don't agree with your practise of assigning the values to unnecessary variables (waste of memory, really).

I would build the criteria like this:

Dim strCriteria As String

strCriteria = "WHERE "

If Not IsNull(Me.cboSender) Then
    strCriteria = strCriteria & ".[SENDER NAME] = """ & Me.cboSender & """ AND "
End If

<repeat for other combos>

[code]If Len(strCriteria) <> 6 Then
    strCriteria = Left$(strCriteria, Len(strCriteria) - 5)
End If

AS you'd have built the select part of the statement from looping through the listbox, you can then concatenate the select statement with the criteria statement.