ConCat question

[d9pierce1]

HI all,
I cannot figure out how to concat this and have tried so many ways?
I marked it in red below

Private Sub CmdReport_Click()
On Error Resume Next


Dim varItem As Variant
Dim strDoc As String


If IsNull(Me.LstAccountReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


   With Me.LstAccountReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With
If Not IsNull(Me.LstAccountType.Column(0)) And (Me.grpFilterOptions = 2) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[AccountTypeID] in(" & getLBX(Me.LstAccountType) & ")"
    
ElseIf Not IsNull(Me.LstAccountType.Column(0)) And (Me.grpFilterOptions = 1) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[AccountTypeID] in(" & getLBX(Me.LstAccountType) & ") AND" & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, , "frmAccountReport"
    
ElseIf Forms!frmAccount.sfrmAccount.Form.chkSingle = True Then
       If Me.grpFilterOptions = 2 Then
            DoCmd.OpenReport strDoc, acViewPreview, , "AccountID=" & Forms!frmAccount!AccountID, , "frmAccountReport"
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , "AccountID=" & Forms!frmAccount!AccountID & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, , "frmAccountReport"
    End If
    
Else
    
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, acNormal, "frmAccountReport"
    End If


End If

Thank you
Dave

[Micron]

I don't get the question. Concatenate what? It looks to me like that should compile ok. Looks ok as long as the form values are not text.

[d9pierce1]

Hi Micron,
It does compile but creates an syntax error when run!
We had a similar problem with the code above it and he corrected it by adding this & ") AND" & so thinking there must be a way of concatenating like that I am missing
"[AccountTypeID] in(" & getLBX(Me.LstAccountType) & ") AND" & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter

[Micron]

How about replacing the references with actual values and type it out as a sentence and not code? Or is this (note the blue and red)
"AccountID=" & Forms!frmAccount!AccountID & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, , "frmAccountReport"
supposed to become like this
"AccountID=" & 59, , "frmAccountReport"

[d9pierce1]

Fixed it
Wow, took all morning to do this!
DoCmd.OpenReport strDoc, acViewPreview, , "AccountID=" & Forms!frmAccount!AccountID & "AND" & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, , "frmAccountReport"

[Micron]

Kinda surprised that works. I would have thought it had to be " AND "
So your criteria is that AccountID = 5 AND 9 ? Because that's what you're saying.

[Welshgasman]

Fixed it
Wow, took all morning to do this!
DoCmd.OpenReport strDoc, acViewPreview, , "AccountID=" & Forms!frmAccount!AccountID & "AND" & Forms![frmAccount].[sfrmAccount].Form.txtReportFilter, , "frmAccountReport"

Cannot see that working either?