Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access OutputTo Report using filter string

Posted on 2010-11-15
28
Medium Priority
?
607 Views
Last Modified: 2012-05-10
Hi there,

I am trying to create an outputto report function for my database. The report currently filters propery when using
DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere but I would like to be able to output this to an excel spreadsheet with the filtered items being displayed.  I have tried using the DoCmd.OutputTo acOutputReport, "reportName", ... format but it doesn't filter at all.

Your help is much appreciated in advance.
0
Comment
Question by:databarracks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 11
28 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34135832
Hi,

Create a select query called SQLOutput - does not matter what the SQL is.

...
DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
CurrentDb.QueryDefs("SQLOutput ").SQL = Reports("rptFilter").SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"
...

This will export the filtered report data shown in Print Preview to excel file C:\xyz.xls

Regards,

Bill
0
 

Author Comment

by:databarracks
ID: 34135885
Could kindly explain what you mean exactly by "Create a select query called SQLOutput - does not matter what the SQL is."?
0
 

Author Comment

by:databarracks
ID: 34135914
I have copied the code into mine and can confirm it is filtering the report into a preview mode. But I do have a run-time error '2465' "Application-defined or object-defined error"?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34136568
This could be many things...
Then can you please post the code you are using?
0
 

Author Comment

by:databarracks
ID: 34136677
Here you go as requested.
Private Sub Command100_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([Country] Like ""*" & Me.txtFilterCity & "*"") AND "
    End If
    
       'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtRating) Then
        strWhere = strWhere & "([Satisfaction] Like ""*" & Me.txtRating & "*"") AND "
    End If
    
        'Text sage code example. Use quotes around the value in the string.
    If Not IsNull(Me.txtSage) Then
        strWhere = strWhere & "([ACCOUNT_REF] Like ""*" & Me.txtSage & "*"") AND "
    End If
    
         'Text Account Manager example. Use quotes around the value in the string.
    If Not IsNull(Me.txtEmployee) Then
        strWhere = strWhere & "([AccountManager] Like ""*" & Me.txtEmployee & "*"") AND "
    End If
    
    
    'Contract Length Search
     If Not IsNull(Me.txtContractLength) Then
        strWhere = strWhere & "([ContractLength] Like  ""*" & Me.txtContractLength & "*"") AND "
    End If
    
    'Contract Length Search
     If Not IsNull(Me.txtAddress) Then
        strWhere = strWhere & "([Address] Like  ""*" & Me.txtAddress & "*"") AND "
    End If
    
    'First Name of Customer Contact
     If Not IsNull(Me.txtFirstName) Then
        strWhere = strWhere & "([FirstName] Like  ""*" & Me.txtFirstName & "*"") AND "
    End If
    
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([CompanyName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If
    
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.cboFilterIsCorporate = -1 Then
        strWhere = strWhere & "([Reseller] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
        strWhere = strWhere & "([Reseller] = False) AND "
    'ElseIf Me.cboFilterIsCorporate = 1 Then
        'strWhere = strWhere & "([Reseller] = Null) AND "
    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([Commencement] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([Commencement] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
    
     'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtLastInvoice) Then
        strWhere = strWhere & "([LastInvoiceAmount] >= " & Format(Me.txtLastInvoice) & ") AND "
    End If
    
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtLastInvoice2) Then   'Less than the next day.
        strWhere = strWhere & "([LastInvoiceAmount] <= " & Format(Me.txtLastInvoice2) & ") AND "
    End If
    

    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
    DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
    CurrentDb.QueryDefs("SQLOutput ").SQL = Reports("rptFilter").SQL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"

End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34136731
Hi,

Just make a select query.  Select one field from any table and save the query with the name SQLOutput.  We need a querydef to output.  We change the sql in code.

Also, chege the line
CurrentDb.QueryDefs("SQLOutput ").SQL = Reports("rptFilter").SQL

to (remove the space after "SQLOutput "
CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").SQL

Regards,

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34136750
Also remove the space in this line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"

to

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"
0
 

Author Comment

by:databarracks
ID: 34136837
Hi Bill,

I have tried that and I got the same error? Could you give me the exact code after the End If because I currently have the attached code. Not too sure where  I should put your suggested code


DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"

Open in new window

0
 

Author Comment

by:databarracks
ID: 34136853
Ok I have removed the space in both and the same error unfortunately
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34137159
Did you create the query?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34137204
Hi,

You only need to add 2 lines of code.  Check your original code.

Does the report show and do you get errors in it?

If not AND if you have created the query named SQLOutput then you would need to put the 2 lines of code after your line of code:
DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere

add these 2 lines:

CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"

Bill
0
 

Author Comment

by:databarracks
ID: 34137243
Yes and I have and named it SQLOutput.
0
 

Author Comment

by:databarracks
ID: 34137528
Yes the report shows perfectly fine, and once I have selected to end the debug I can convert it to an excel file, it is just that other users may not know what to do when the error appears.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34137746
Hi,

Please determine which line of code is giving you the error.

Comment out the first line then the second and see if either gives the error.

Bill
0
 

Author Comment

by:databarracks
ID: 34137871
I have commented out 'CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").SQL' and it seems to be working fine. No errors popping up, however the .xls report doesn't appear to be filtered and is showing all records.
0
 

Author Comment

by:databarracks
ID: 34137951
Would this be caused by my SQLOutput query?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34138252
Hi,

Try this slight change to see if the error corrects:

Replace
CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").SQL

with
CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").recordsource

If you still get the error post the SQL for the query SQLOutput

Bill
0
 

Author Comment

by:databarracks
ID: 34143081
Unfortunately the error is persisting. The new error is 'Invalid SQL statement; expected 'DELETE', 'INSERT','PROCEDURE','SELECT', or 'UPDATE'. The SQL for my query is attached.
SELECT Company.ID, Company.CompanyName, Company.ContractExpiryDate, Company.FirstName, Company.[E-mail], Company.ContractLength, Company.AccountManager, Company.Commencement, Company.LastInvoiceAmount
FROM Company;

Open in new window

0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34144023
Hi,

OK.  The query is correct so I susect you have an incorrect filter that the report passes but the query fails.

Replace this line:
CurrentDb.QueryDefs("SQLOutput").SQL = Reports("rptFilter").recordsource

with these 2 lines:
debug.print Reports("rptFilter").recordsource
stop

The code will stop and the debug window will show the SQL for the filtered report.  Post this code.

Bill
0
 

Author Comment

by:databarracks
ID: 34144462
My immediate window shows 'Company'
0
 

Author Comment

by:databarracks
ID: 34144478
This is what I currently have for my code.
DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
    Debug.Print Reports("rptFilter").RecordSource
    Stop
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"

Open in new window

0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34144511
Hi,
Replace
Debug.Print Reports("rptFilter").RecordSource
With
Debug.Print strWhere

This will aloow up to look at the where clause.

Bill
0
 

Author Comment

by:databarracks
ID: 34144526
Yes I ahve changed this and had a filter for first name called mark and received this:

([FirstName] Like  "*mark*")
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34144584
Hi,

Lastly let's look at the reports SQL statement.

Debug.Print Reports("rptFilter").SQL

Bill
0
 

Author Comment

by:databarracks
ID: 34144707
I replaced 'Debug.Print strWhere' with 'Debug.Print Reports("rptFilter").SQL' and got an Application-defined or object-defined error
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 34145724
Hi,

OK.  We can make a correct SQL statement from the strWhere.  Replace the last lines in your code with:

DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
CurrentDb.QueryDefs("SQLOutput").SQL ="SELECT * FROM Company WHERE " & strWhere
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"

Bill


0
 

Author Comment

by:databarracks
ID: 34146357
Hi Bill,

That has worked perfectly Bill. Thank you very much for your help and patience with this issue,  it has been much appreciated.
0
 

Author Closing Comment

by:databarracks
ID: 34146369
Very helpful with my problem.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question