Report based on search string built in form 1

[smalty]

thread703-1476741

The above thread was very useful to me. However I am using the same code that Hookum provided and I am getting an error.

The code is basically doing exactly the same as before and is being started on a button click. The first time I click the button after opening the form and entering search criteria, I get the report as expected. The form remains open and if I go back to it, clear the search criteria re-select another set of criteria and then click the report button again I get the error.......

Runtime error 3075

Syntax error (missing operator) in query expression '(1=1 AND Amount = AND ExpID= "" AND etc etcetc )'.

The debug highlites the line
DoCmd. OpenReport strReportName, acPreview, , strWhere.

If I close the form and reopen it and enter new search terms and click the button it works.

Any help appreciated.

Smalty

 

[dhookom]

Share your code. You are including some type of amount filter without selecting anything.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Hi dhookom,
The code is this
Private Sub Command35_Click()
Dim strWhere As String
Dim strReportName As String
strReportName = "rViewExp"
strWhere = "1=1 "
If Not IsNull(Me.qAmount) Then
'EmpID is numeric
strWhere = strWhere & " AND Amount = " & Me.qAmount
End If
If Not IsNull(Me.qExpID) Then
'DeptName is text
strWhere = strWhere & " AND ExpID = """ & _
Me.qExpID & """"
End If
If Not IsNull(Me.qVendor) Then
'DeptName is text
strWhere = strWhere & " AND Vendor = """ & _
Me.qVendor & """"
End If
If Not IsNull(Me.qExpCat) Then
'DeptName is text
strWhere = strWhere & " AND Exp Category = """ & _
Me.qExpCat & """"
End If
If Not IsNull(Me.qExpItem) Then
'DeptName is text
strWhere = strWhere & " AND Item = """ & _
Me.qExpItem & """"
End If
If Not IsNull(Me.qTransType) Then
'DeptName is text
strWhere = strWhere & " AND Trans = """ & _
Me.qTransType & """"
End If
If Not IsNull(Me.qTransRef) Then
'DeptName is text
strWhere = strWhere & " AND Trans Ref No = """ & _
Me.qTransRef & """"
End If
If Not IsNull(Me.qDocRec) Then
'DeptName is text
strWhere = strWhere & " AND Document Receipt = """ & _
Me.qDocRec & """"
End If
If Not IsNull(Me.qDateF) Then
'DateField is a date
strWhere = strWhere & " And [Date]>=#" & Format(Me.qDateF, "yyyy-mm-dd") & "#"
End If
If Not IsNull(Me.qDateT) Then
'DateField is a date
strWhere = strWhere & " And [Date]<=#" & Format(Me.qDateT, "yyyy-mm-dd") & "#"
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
End Sub

 

[dhookom]

Consider four things:
[ul]
[li]Learning how to use TGML to format your code blocks so they are easier to read and understand[/li]
[li]Finding and using a naming convention that doesn't allow spaces and special characters in object/field/table names[/li]
[li]If you have spaces and special characters wrap your names in []s[/li]
[li]Update your comments so they make sense[/li]
[/ul]

Private Sub Command35_Click()
    Dim strWhere As String
    Dim strReportName As String
    strReportName = "rViewExp"
    strWhere = "1=1 "
    If Not IsNull(Me.qAmount) Then
        [COLOR=#4E9A06]'Amount is numeric[/color]
        strWhere = strWhere & " AND Amount = " & Me.qAmount
    End If
    If Not IsNull(Me.qExpID) Then
        [COLOR=#4E9A06]'ExpID is text[/color]
        strWhere = strWhere & " AND ExpID = """ & _
        Me.qExpID & """"
    End If
    If Not IsNull(Me.qVendor) Then
       [COLOR=#4E9A06] 'DeptName is text[/color]
        strWhere = strWhere & " AND Vendor = """ & _
        Me.qVendor & """"
    End If
    If Not IsNull(Me.qExpCat) Then
        [COLOR=#4E9A06]'Exp Category is text[/color]
        strWhere = strWhere & " AND [Exp Category] = """ & _
        Me.qExpCat & """"
    End If
    If Not IsNull(Me.qExpItem) Then
        [COLOR=#4E9A06]'Item is text[/color]
        strWhere = strWhere & " AND Item = """ & _
        Me.qExpItem & """"
    End If
    If Not IsNull(Me.qTransType) Then
        [COLOR=#4E9A06]'Trans  is text[/color]
        strWhere = strWhere & " AND Trans = """ & _
        Me.qTransType & """"
    End If
    If Not IsNull(Me.qTransRef) Then
        [COLOR=#4E9A06]'Trans Ref No is text[/color]
        strWhere = strWhere & " AND [Trans Ref No] = """ & _
        Me.qTransRef & """"
    End If
    If Not IsNull(Me.qDocRec) Then
        [COLOR=#4E9A06]'Trans Ref No is text[/color]
        strWhere = strWhere & " AND [Trans Ref No] = """ & _
        Me.qDocRec & """"
    End If
    If Not IsNull(Me.qDateF) Then
        [COLOR=#4E9A06]'Date is a date[/color]
        strWhere = strWhere & " And [Date]>=#" & Format(Me.qDateF, "yyyy-mm-dd") & "#"
    End If
    If Not IsNull(Me.qDateT) Then
        [COLOR=#4E9A06]'Date is a date[/color]
        strWhere = strWhere & " And [Date]<=#" & Format(Me.qDateT, "yyyy-mm-dd") & "#"
    End If
    DoCmd.OpenReport strReportName, acPreview, , strWhere
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,
I accept your 'observations' and understand about not using spaces and special characters.....poor effort on my part.....and have changed those fields to be enclosed in []. I have also made the Comments more realistic......but the problem does still exist. It works exactly as I would expect the first time after opening the form but not subsequent times


Private Sub Command35_Click()
Dim strWhere As String
Dim strReportName As String
strReportName = "rViewExp"
strWhere = "1=1 "
If Not IsNull(Me.qAmount) Then
'Amount is numeric
strWhere = strWhere & " AND Amount = " & Me.qAmount
End If
If Not IsNull(Me.qExpID) Then
'ExpID is text
strWhere = strWhere & " AND ExpID = """ & _
Me.qExpID & """"
End If
If Not IsNull(Me.qVendor) Then
'Vendor is text
strWhere = strWhere & " AND Vendor = """ & _
Me.qVendor & """"
End If
If Not IsNull(Me.qExpCat) Then
'Exp Category is text
strWhere = strWhere & " AND [Exp Category] = """ & _
Me.qExpCat & """"
End If
If Not IsNull(Me.qExpItem) Then
'Item is text
strWhere = strWhere & " AND Item = """ & _
Me.qExpItem & """"
End If
If Not IsNull(Me.qTransType) Then
'Trans is text
strWhere = strWhere & " AND Trans = """ & _
Me.qTransType & """"
End If
If Not IsNull(Me.qTransRef) Then
'Trans Ref No is text
strWhere = strWhere & " AND [Trans Ref No] = """ & _
Me.qTransRef & """"
End If
If Not IsNull(Me.qDocRec) Then
'Document Receipt is text
strWhere = strWhere & " AND [Document Receipt] = """ & _
Me.qDocRec & """"
End If
If Not IsNull(Me.qDateF) Then
'Date is a date
strWhere = strWhere & " And [Date]>=#" & Format(Me.qDateF, "yyyy-mm-dd") & "#"
End If
If Not IsNull(Me.qDateT) Then
'Date is a date
strWhere = strWhere & " And [Date]<=#" & Format(Me.qDateT, "yyyy-mm-dd") & "#"
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere
End Sub

 

[dhookom]

What are all the "q" items? are these text boxes? Do you have a default set to something like a blank space or ""?
What happens if you use:

    [COLOR=#4E9A06]'assuming you have a blank space rather than Null[/color]
    If Len(Trim(Me.qAmount & "")) > 0 Then
        [COLOR=#4E9A06]'Amount is numeric[/color]
        strWhere = strWhere & " AND Amount = " & Me.qAmount
    End If

Please take a look at the TGML help. It's really easy to use. Just select your code block and click on the Code button.

It's also important to understand faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Hi dhookom,
All the q's are text boxes and when I clear these boxes for a new search it uses "".

I have to get some sleep now so will try your code in the morning but based on what you are saying it should work.

Thanks as usual for your prompt assistance and advice and patience. I will definitely check out the TGML stuff.

Will advise on the success of the new code tomorrow.

Thanks again

Smalty

 

[dhookom]

Don't blank out the text boxes with "". Use Null.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,
It was the 'not "" use Null'.

Excellent.....thank you very much again

Smalty