databarracks
asked on
MS Access OutputTo Report using filter string
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.
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.
ASKER
Could kindly explain what you mean exactly by "Create a select query called SQLOutput - does not matter what the SQL is."?
ASKER
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"?
This could be many things...
Then can you please post the code you are using?
Then can you please post the code you are using?
ASKER
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
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("SQLOu tput ").SQL = Reports("rptFilter").SQL
to (remove the space after "SQLOutput "
CurrentDb.QueryDefs("SQLOu tput").SQL = Reports("rptFilter").SQL
Regards,
Bill
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("SQLOu
to (remove the space after "SQLOutput "
CurrentDb.QueryDefs("SQLOu
Regards,
Bill
Also remove the space in this line:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"
to
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput ", "C:\xyz.xls"
to
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"
ASKER
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
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"
ASKER
Ok I have removed the space in both and the same error unfortunately
Did you create the query?
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("SQLOu tput").SQL = Reports("rptFilter").SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"
Bill
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("SQLOu
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SQLOutput", "C:\xyz.xls"
Bill
ASKER
Yes and I have and named it SQLOutput.
ASKER
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.
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
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
ASKER
I have commented out 'CurrentDb.QueryDefs("SQLO utput").SQ L = 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.
ASKER
Would this be caused by my SQLOutput query?
Hi,
Try this slight change to see if the error corrects:
Replace
CurrentDb.QueryDefs("SQLOu tput").SQL = Reports("rptFilter").SQL
with
CurrentDb.QueryDefs("SQLOu tput").SQL = Reports("rptFilter").recor dsource
If you still get the error post the SQL for the query SQLOutput
Bill
Try this slight change to see if the error corrects:
Replace
CurrentDb.QueryDefs("SQLOu
with
CurrentDb.QueryDefs("SQLOu
If you still get the error post the SQL for the query SQLOutput
Bill
ASKER
Unfortunately the error is persisting. The new error is 'Invalid SQL statement; expected 'DELETE', 'INSERT','PROCEDURE','SELE CT', 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;
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("SQLOu tput").SQL = Reports("rptFilter").recor dsource
with these 2 lines:
debug.print Reports("rptFilter").recor dsource
stop
The code will stop and the debug window will show the SQL for the filtered report. Post this code.
Bill
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("SQLOu
with these 2 lines:
debug.print Reports("rptFilter").recor
stop
The code will stop and the debug window will show the SQL for the filtered report. Post this code.
Bill
ASKER
My immediate window shows 'Company'
ASKER
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"
Hi,
Replace
Debug.Print Reports("rptFilter").Recor dSource
With
Debug.Print strWhere
This will aloow up to look at the where clause.
Bill
Replace
Debug.Print Reports("rptFilter").Recor
With
Debug.Print strWhere
This will aloow up to look at the where clause.
Bill
ASKER
Yes I ahve changed this and had a filter for first name called mark and received this:
([FirstName] Like "*mark*")
([FirstName] Like "*mark*")
Hi,
Lastly let's look at the reports SQL statement.
Debug.Print Reports("rptFilter").SQL
Bill
Lastly let's look at the reports SQL statement.
Debug.Print Reports("rptFilter").SQL
Bill
ASKER
I replaced 'Debug.Print strWhere' with 'Debug.Print Reports("rptFilter").SQL' and got an Application-defined or object-defined error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Bill,
That has worked perfectly Bill. Thank you very much for your help and patience with this issue, it has been much appreciated.
That has worked perfectly Bill. Thank you very much for your help and patience with this issue, it has been much appreciated.
ASKER
Very helpful with my problem.
Create a select query called SQLOutput - does not matter what the SQL is.
...
DoCmd.OpenReport "rptFilter", acViewPreview, , strWhere
CurrentDb.QueryDefs("SQLOu
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