Error 3075 : Working for search, but not report

N

Nyanko

Registered User.
Local time
Today, 03:10
Joined
Apr 21, 2005
Messages
57
Hi
I'm using an adapted version of this search form :

http://www.access-programmers.co.uk/forums/showthread.php?t=99353

It works how I expect, except that when trying to run a report based on the search results I get the error
"Run-time error 3075 - syntax error (missing operator) in query expression"

I'm not hugely familiar with this function and hope that someone can help.

Here is my code to run the function based on a Search or Report button:
Code: 
Private Sub btn_Search_Click()
 
 ' Update the record source
On Error Resume Next
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch " & BuildFilter
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch WHERE " & BuildFilter
On Error GoTo 0
       
    ' Requery the subform
    Me.frm_ClientSearchSub.Requery
End Sub



Private Sub btn_Report_Click()
    Dim stDocName As String
    
On Error Resume Next
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch " & BuildFilter
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch WHERE " & BuildFilter
On Error GoTo 0
    
    Debug.Print BuildFilter
    stDocName = "Rpt_ClientSearch"
    DoCmd.OpenReport stDocName, acNormal, WhereCondition:=BuildFilter
End Sub

the debug shows a result of :
( [StatusType] = "Outstanding" ) ORDER BY [ScheduledDate] ASC;


This is the Buildfilter function :
Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varStatus As Variant
    Dim varItem As Variant
    Dim varOrder As Variant
    Dim varDirection As Variant
    Dim intIndex As Integer

    varWhere = Null     ' Main filter
    varStatus = Null    ' Subfilter used for Status'
    varOrder = Null     ' Sort by
    varDirection = Null ' Sort Direction
    
    ' Check for LIKE Project code
    If Me.txtProjectCode > "" Then
        varWhere = varWhere & "[ProjectCode] LIKE """ & Me.txtProjectCode & "*"" AND "
    End If
   
    ' Check for LIKE InvoiceID
    If Me.txtInvoiceID > "" Then
        varWhere = varWhere & "[InvoiceID] LIKE """ & Me.txtInvoiceID & "*"" AND "
    End If
   
    ' Check for LIKE RowID
    If Me.txtRowID > "" Then
        varWhere = varWhere & "[RowID] LIKE """ & Me.txtRowID & "*"" AND "
    End If
   
    ' Check for LIKE RecordID
    If Me.txtRecordID > "" Then
        varWhere = varWhere & "[RecordID] LIKE """ & Me.txtRecordID & "*"" AND "
    End If
   
    ' Check Dates Greater Than
    If Me.txtGreaterThan > "" Then
        varWhere = varWhere & "[ScheduledDate] >= #" & Format(Me.txtGreaterThan, "mm/dd/yyyy") & "# AND "
    End If

    ' Check Dates Less Than
    If Me.txtLessThan > "" Then
        varWhere = varWhere & "[ScheduledDate] <= #" & Format(Me.txtLessThan, "mm/dd/yyyy") & "# AND "
    End If

    ' Check for Third Paty
    If Me.cmbThirdParty <> "<ALL>" Then
        varWhere = varWhere & "[ThirdParty] = """ & Me.cmbThirdParty & """ AND "
    End If

    ' Check for Account
    If Me.cmbAccount <> "<ALL>" Then
        varWhere = varWhere & "[AccountName] = """ & Me.cmbAccount & """ AND "
    End If

    ' Check for Status in multiselect list
    For Each varItem In Me.lstStatus.ItemsSelected
        varStatus = varStatus & "[Status] = """ & Me.lstStatus.ItemData(varItem) & """ OR "
    Next

    ' Check for Transaction Type in multiselect list
    For Each varItem In Me.lstType.ItemsSelected
        varStatus = varStatus & "[StatusType] = """ & Me.lstType.ItemData(varItem) & """ OR "
    Next

    ' Check for Account
    If Me.cmbTransactionType <> "<ALL>" Then
        varWhere = varWhere & "[TransactionType] = """ & Me.cmbTransactionType & """ AND "
    End If


    'BUILD THE VARIABLE, ADJUSTING ANY FILTERS AS NEEDED

    ' Test to see if we have subfilter for Status...
    If IsNull(varStatus) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varStatus, 4) = " OR " Then
            varStatus = Left(varStatus, Len(varStatus) - 4)
        End If

        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varStatus & " )"
    End If

    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
        
    'Build the Sort Order
    If Me.cmbSortOrder > "" Then
        Select Case Me.cmbSortOrder
            Case "ACCOUNT NAME"
                varOrder = "[AccountName]"
            Case "INVOICE NUMBER"
                varOrder = "[InvoiceID]"
            Case "PAID DATE"
                varOrder = "[PaidDate]"
            Case "PAYMENT STATUS"
                varOrder = "[PaymentStatus]"
            Case "PAYMENT TYPE"
                varOrder = "[TransactionType]"
            Case "PROJECT CODE"
                varOrder = "[ProjectCode]"
            Case "RECORD ID"
                varOrder = "[RecordID]"
            Case "ROW ID"
                varOrder = "[RowID]"
            Case "SCHEDULED DATE"
                varOrder = "[ScheduledDate]"
            Case "THIRD PARTY"
                varOrder = "[ThirdParty]"
            Case "VALUE"
                varOrder = "[InvoiceValue]"
        End Select
    End If

    'Build the Sort Direction
    If Me.cmbSortDirection = "" Then
            varDirection = "DESC"
        Else
            If Me.cmbSortDirection = "ASCENDING" Then varDirection = "ASC" Else If Me.cmbSortDirection = "DESCENDING" Then varDirection = "DESC"
    End If
    
    'Passes the SQL Select Statement back to the BuildFilter variable
    If Me.cmbSortOrder > "" And Me.cmbSortDirection > "" Then
            BuildFilter = varWhere & " ORDER BY " & varOrder & " " & varDirection & ";"
        Else
            BuildFilter = varWhere & " ORDER BY [ScheduledDate] ASC;"
    End If
End Function


When the Search button is clicked the subform displays the information exactly as I would expect. However when trying to run the report on the same filter I get the above error.

Can anyone help me with a solution ?
 
What is the Record source for the report?
 
A query (Qry_ClientSearch) that controls the subform
 
Show the SQL-String for the query Qry_ClientSearch.
Else post your database with some sample data, zip it.
 
Code: 
SELECT 
tbl_MainData.RowID, 
tbl_MainData.RecordID, 
tbl_MainData.InvoiceID, 
tbl_MainData.ScheduledDate, 
tbl_MainData.ProjectCode, 
lkp_ProjectList.ThirdParty, 
lkp_ProjectList.AccountName, 
tbl_MainData.InvoiceValue, 
tbl_MainData.Commissionable, 
tbl_MainData.TransactionType, 
tbl_MainData.Status, 
tbl_MainData.Paid, 
tbl_MainData.PaidDate, 
IIf([Paid]=True,"Paid",IIf((Date()-[DueDate])>45,"Escalate",IIf((Date()-[DueDate])>=37,"Phone Call",IIf((Date()-[DueDate])>=30,"Third Email",IIf((Date()-[DueDate])>=18,"Second Email",IIf((Date()-[DueDate])>7,"First Email",IIf((Date()-[DueDate])>=1,"Statement of Account","Not Due"))))))) AS PaymentStatus, 
lkp_Status.StatusType

FROM 
(lkp_ProjectList INNER JOIN tbl_MainData ON lkp_ProjectList.ProjectCode = tbl_MainData.ProjectCode) INNER JOIN lkp_Status ON tbl_MainData.Status = lkp_Status.StatusDescription
ORDER BY tbl_MainData.ScheduledDate;
 
Have look at your code again, the problem is the "ORDER BY [ScheduledDate] ASC", then it is not a where conditioner.
the debug shows a result of :
( [StatusType] = "Outstanding" ) ORDER BY [ScheduledDate] ASC;
Click to expand...
 
Ahh I see.

By removing the sort order/direction it works again.

Thank you !
 
For my own reference I added a report flag to the Private Sub btn_Report_Click()

Code: 
Private Sub btn_Report_Click()
    Dim stDocName As String
    Dim ReportFlag As Boolean
    
    ReportFlag = True
On Error Resume Next
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch " & BuildFilter(ReportFlag)
        Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch WHERE " & BuildFilter(ReportFlag)
On Error GoTo 0
    
    stDocName = "Rpt_ClientSearch"
    DoCmd.OpenReport stDocName, acViewReport, WhereCondition:=BuildFilter(ReportFlag)
End Sub

then used it in the main function to bypass the sort order/direction
Code: 
Private Function BuildFilter(Optional ReportFlag As Boolean) As Variant
    Dim varWhere As Variant
    Dim varStatus As Variant
    Dim varItem As Variant
    Dim varOrder As Variant
    Dim varDirection As Variant
    Dim intIndex As Integer

    varWhere = Null     ' Main filter
    varStatus = Null    ' Subfilter used for Status'
    varOrder = Null     ' Sort by
    varDirection = Null ' Sort Direction
    
    ' Check for LIKE Project code
    If Me.txtProjectCode > "" Then
        varWhere = varWhere & "[ProjectCode] LIKE """ & Me.txtProjectCode & "*"" AND "
    End If
   
    ' Check for LIKE InvoiceID
    If Me.txtInvoiceID > "" Then
        varWhere = varWhere & "[InvoiceID] LIKE """ & Me.txtInvoiceID & "*"" AND "
    End If
   
    ' Check for LIKE RowID
    If Me.txtRowID > "" Then
        varWhere = varWhere & "[RowID] LIKE """ & Me.txtRowID & "*"" AND "
    End If
   
    ' Check for LIKE RecordID
    If Me.txtRecordID > "" Then
        varWhere = varWhere & "[RecordID] LIKE """ & Me.txtRecordID & "*"" AND "
    End If
   
    ' Check Dates Greater Than
    If Me.txtGreaterThan > "" Then
        varWhere = varWhere & "[ScheduledDate] >= #" & Format(Me.txtGreaterThan, "mm/dd/yyyy") & "# AND "
    End If

    ' Check Dates Less Than
    If Me.txtLessThan > "" Then
        varWhere = varWhere & "[ScheduledDate] <= #" & Format(Me.txtLessThan, "mm/dd/yyyy") & "# AND "
    End If

    ' Check for Third Paty
    If Me.cmbThirdParty <> "<ALL>" Then
        varWhere = varWhere & "[ThirdParty] = """ & Me.cmbThirdParty & """ AND "
    End If

    ' Check for Account
    If Me.cmbAccount <> "<ALL>" Then
        varWhere = varWhere & "[AccountName] = """ & Me.cmbAccount & """ AND "
    End If

    ' Check for Status in multiselect list
    For Each varItem In Me.lstStatus.ItemsSelected
        varStatus = varStatus & "[Status] = """ & Me.lstStatus.ItemData(varItem) & """ OR "
    Next

    ' Check for Transaction Type in multiselect list
    For Each varItem In Me.lstType.ItemsSelected
        varStatus = varStatus & "[StatusType] = """ & Me.lstType.ItemData(varItem) & """ OR "
    Next

    ' Check for Account
    If Me.cmbTransactionType <> "<ALL>" Then
        varWhere = varWhere & "[TransactionType] = """ & Me.cmbTransactionType & """ AND "
    End If


    'BUILD THE VARIABLE, ADJUSTING ANY FILTERS AS NEEDED

    ' Test to see if we have subfilter for Status...
    If IsNull(varStatus) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varStatus, 4) = " OR " Then
            varStatus = Left(varStatus, Len(varStatus) - 4)
        End If

        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varStatus & " )"
    End If

    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
        
    BuildFilter = varWhere
    If ReportFlag = True Then GoTo EndFunction
        
    'Build the Sort Order
    If Me.cmbSortOrder > "" Then
        Select Case Me.cmbSortOrder
            Case "ACCOUNT NAME"
                varOrder = "[AccountName]"
            Case "INVOICE NUMBER"
                varOrder = "[InvoiceID]"
            Case "PAID DATE"
                varOrder = "[PaidDate]"
            Case "PAYMENT STATUS"
                varOrder = "[PaymentStatus]"
            Case "PAYMENT TYPE"
                varOrder = "[TransactionType]"
            Case "PROJECT CODE"
                varOrder = "[ProjectCode]"
            Case "RECORD ID"
                varOrder = "[RecordID]"
            Case "ROW ID"
                varOrder = "[RowID]"
            Case "SCHEDULED DATE"
                varOrder = "[ScheduledDate]"
            Case "THIRD PARTY"
                varOrder = "[ThirdParty]"
            Case "VALUE"
                varOrder = "[InvoiceValue]"
        End Select
    End If

    'Build the Sort Direction
    If Me.cmbSortDirection = "" Then
            varDirection = "DESC"
        Else
            If Me.cmbSortDirection = "ASCENDING" Then varDirection = "ASC" Else If Me.cmbSortDirection = "DESCENDING" Then varDirection = "DESC"
    End If

    'Passes the SQL Select Statement back to the BuildFilter variable
    If Me.cmbSortOrder > "" And Me.cmbSortDirection > "" Then
            BuildFilter = varWhere & " ORDER BY " & varOrder & " " & varDirection & ";"
        Else
            BuildFilter = varWhere & " ORDER BY [ScheduledDate] ASC;"
    End If
    
EndFunction:
End Function
 
You must log in or register to reply here.

Similar threads

oxicottin
Cant get form filter to work correctly
Replies
8
Views
376
plog
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
850
oxicottin
oxicottin
C
Working in 32 bit but not in 64.
Replies
19
Views
369
Catalina
C
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
706
tvanstiphout
T
oxicottin
Search Form errors for date.
Replies
16
Views
1,360
Micron
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom