How do I filter an Access report?

  • Thread starter Thread starter Meshman
  • Start date Start date
M

Meshman

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#
Click to expand...

.... 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!
 
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 said:
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#
Click to expand...

... 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!
Click to expand...
 
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
(e-mail address removed)



Meshman said:
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#
Click to expand...

... 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!
Click to expand...
 
Meshman said:
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#
Click to expand...

... 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. - ?
Click to expand...


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?)
 
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 said:
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#
Click to expand...

... 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!
Click to expand...
 
Back
Top