How do I filter an Access report?

How do I apply a filter to an Access report?  I seriously cannot find any 
examples anywhere.  Well, MS says this works:

>#2/2/2006# and <#2/4/2006#

.... but all I get is "missing operator".  It then strikes me I haven't told 
it what to filter on.  What's the syntax for it?  I have a table called 
"Inventory Transactions" and need the field "Transaction Date" filtered by 
the above criteria (different date range but the above example will do).  

I tried this:

[Inventory Transactions].[Transaction Date]>=#1/1/2009# And [Inventory 
Transactions].[Transaction Date]<=#12/31/2009#

....but all it does is prompt me for a value. - ?

Any ideas?
Thanks!
0
Utf
1/15/2010 6:53:01 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
2040 Views

Similar Articles

[PageSpeed] 42

You can filter a report by filtering the query the report is based on. What 
value are you getting prompted for?

I typically open a report with code using the DoCmd.OpenReport method. You 
can add a WHERE CONDITION to this. 
Dim strWhere as String
strWhere = "[Transaction Date] Between #1/1/2009# And #12/31/2009#"
DoCmd.OpenReport "rptMyReportName", acPrintPreview , , strWhere


-- 
Duane Hookom
Microsoft Access MVP


"Meshman" wrote:

> How do I apply a filter to an Access report?  I seriously cannot find any 
> examples anywhere.  Well, MS says this works:
> 
> >#2/2/2006# and <#2/4/2006#
> 
> ... but all I get is "missing operator".  It then strikes me I haven't told 
> it what to filter on.  What's the syntax for it?  I have a table called 
> "Inventory Transactions" and need the field "Transaction Date" filtered by 
> the above criteria (different date range but the above example will do).  
> 
> I tried this:
> 
> [Inventory Transactions].[Transaction Date]>=#1/1/2009# And [Inventory 
> Transactions].[Transaction Date]<=#12/31/2009#
> 
> ...but all it does is prompt me for a value. - ?
> 
> Any ideas?
> Thanks!
0
Utf
1/15/2010 7:24:01 PM
Create a query based on your table called "Inventory Transactions" and 
include all the fields including "Transaction Date" that you want to show in 
the report. Set the criteria for "Transaction Date" to:
Between #2/2/2006# And #2/4/2006#

Probably you want to set the dates on the form from which you open the 
report and then set the criteria to:
Between Forms!FormName!StartDate And Forms!FormName!EndDate

Or you could do this:
Between [Enter Start Date] And [Enter End Date]
and two dialogs will appear asking for the Start Date and End Date.

Steve
santus@penn.com



"Meshman" <Meshman@discussions.microsoft.com> wrote in message 
news:48995691-2EEB-465D-AE1C-A37A3621E73D@microsoft.com...
> How do I apply a filter to an Access report?  I seriously cannot find any
> examples anywhere.  Well, MS says this works:
>
>>#2/2/2006# and <#2/4/2006#
>
> ... but all I get is "missing operator".  It then strikes me I haven't 
> told
> it what to filter on.  What's the syntax for it?  I have a table called
> "Inventory Transactions" and need the field "Transaction Date" filtered by
> the above criteria (different date range but the above example will do).
>
> I tried this:
>
> [Inventory Transactions].[Transaction Date]>=#1/1/2009# And [Inventory
> Transactions].[Transaction Date]<=#12/31/2009#
>
> ...but all it does is prompt me for a value. - ?
>
> Any ideas?
> Thanks! 


0
Steve
1/15/2010 7:30:55 PM
Meshman wrote:

>How do I apply a filter to an Access report?  I seriously cannot find any 
>examples anywhere.  Well, MS says this works:
>
>>#2/2/2006# and <#2/4/2006#
>
>... but all I get is "missing operator".  It then strikes me I haven't told 
>it what to filter on.  What's the syntax for it?  I have a table called 
>"Inventory Transactions" and need the field "Transaction Date" filtered by 
>the above criteria (different date range but the above example will do).  
>
>I tried this:
>
>[Inventory Transactions].[Transaction Date]>=#1/1/2009# And [Inventory 
>Transactions].[Transaction Date]<=#12/31/2009#
>
>...but all it does is prompt me for a value. - ?


I think you put that filter criteria in the wrong place, but
you did not say where/how you are trying to use that
criteria.

When you are prompted for sumething, it means that the thing
you are prompted for is not in the report's record source
table/query (a typo?)

-- 
Marsh
MVP [MS Access]
0
Marshall
1/15/2010 8:16:09 PM
Apparently, this is the answer:

"[Inventory Transactions].[Transaction Date] >= #" & 1/1/2009 & "#" & " And 
[Inventory 
Transactions].[Transaction Date] <= # " & 12/31/2009 & "#"

....and it doesn't work either but it doesn't return any errors.  The report 
contains ALL records, even with this criteria.  I had to go into the 
recordsource and add the criteria in there.


"Meshman" wrote:

> How do I apply a filter to an Access report?  I seriously cannot find any 
> examples anywhere.  Well, MS says this works:
> 
> >#2/2/2006# and <#2/4/2006#
> 
> ... but all I get is "missing operator".  It then strikes me I haven't told 
> it what to filter on.  What's the syntax for it?  I have a table called 
> "Inventory Transactions" and need the field "Transaction Date" filtered by 
> the above criteria (different date range but the above example will do).  
> 
> I tried this:
> 
> [Inventory Transactions].[Transaction Date]>=#1/1/2009# And [Inventory 
> Transactions].[Transaction Date]<=#12/31/2009#
> 
> ...but all it does is prompt me for a value. - ?
> 
> Any ideas?
> Thanks!
0
Utf
1/15/2010 8:29:02 PM
Reply:

Similar Artilces: