Report generated via a query

Hot Topic

Error with Data refreshing in the background?
Passing commands to unix from VBA EXCEL
pass parameter to stored procedure
ms access counting specific values on a subform for display
passing a xml document from Excel to Oracle *urgent help please*
Display a drawing on an MS Access Form
Extracting Symbols from Word
extract office file's title into excel
Opening a Word file from a macro in Excel
Closeing shared files

Microsoft ISV

ListParagraphs collection in table
check if workbook is open
VBA: Timer event?
Error with Data refreshing in the background?
ISV Competency Partners left behind for Office 12 Beta
Strange Error
Problem with Defined names
% of public market applications which developed by .net languages?
Architecture - what it is and what it will be
Fastest Processor for Running Excel VBA Macros

Report generated via a query

I have a report that shows all locations and there associated assets however i would like to limit the locations. currently i return everything but this is a waste of paper and sometimes only 1 or 2 locations will be needed is there a way to control the report so only the slected locations are displayed

I think this is relatively easy to accomplish but i cant seem to figure it out fully any help would be much appreciated thanks (again).




Answer this question

Report generated via a query

  • Opher

    Thanks a lot for this reply i knew it was something simple :) i had been adding the sql code in a deifferent place and had no idea how to make it influence the report itself.

    thanks for the help Derek much appreciated.



  • dondi81

    Your right it is relatively easy to do. Queries are SQL statements, SQL is a language thats used to communicate with data sources. It's fairly straight forward. One of the clauses in SQL is the WHERE clause. This is essentially a filter. WHERE [Field] = True, for example only shows the records where Field is true. What your looking to do is to set this just prior to opening your report, how you do that depends on how your reports are opened.

    If your users just double click the report to open then you've got to change the query directly. In the query design type [Please enter value] in the criteria of the field you want to filter and run it, now the user needs to type in a filter value directly. I wouldn't recommend this approach as the query might be used elsewhere and that could break things.

    I recommend you use some vba code to open the report. Create a new form and draw a button on it, double click the button, and type....

    DoCmd.OpenReport "NameOfReport", acViewPreview, , "[Field] = true",

    Except replace "NameOfReport" with ummm the name of your report, and change the WHERE clause "[Field] = true" for whatever column and value you want to filter on, if your column is a text field then make sure to wrap the value in single quotes. When you open the report the WHERE clause is appended to the reports query and your results are filtered.

    Your filter will change so you'll want to feed the WHERE clause with a dynamic value. Draw a textbox and rename it txtFilter. Now change your OpenReport statement to...

    DoCmd.OpenReport "NameOfReport", acViewPreview, , "[Field] = " & txtFilter.Value

    This will take what was typed into the text box and use it to filter the records. You can change the acViewPreview to have the report print directly without the preview.

    If you want to filter by two or more values look up the WHERE clause in the help file.



  • Report generated via a query
Answer this question