?
Solved

Filter Reports vba

Posted on 2012-08-20
8
Medium Priority
?
642 Views
Last Modified: 2012-08-20
I need some help in a very simple problem, but I cant seem to get it to work for me.  I am trying to open a report and have it filter wkstorenumber=3268.  It will open report, but will not put a filter in.  It is almost like it skips that part.  Any help would be greatly appreciated.
Thanks in advance.  

Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "wkstorenumber= " & "3268"
   DoCmd.OpenReport stDocName, acViewPreview, stlinkcriteria
0
Comment
Question by:russell12
  • 5
  • 2
8 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38314625
You're missing a comma.

Try this:

 DoCmd.OpenReport stDocName, acViewPreview,, stlinkcriteria
0
 
LVL 8

Expert Comment

by:5teveo
ID: 38314632
I believe you have a formatting / syntax problem...

Refer to this thread for help

http://bytes.com/topic/access/answers/845477-docmd-openreport

good luck.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38314643
Alternatively, use named arguments and number of commas  / placement of arguments will not be an issue:

 DoCmd.OpenReport ReportName:=stDocName,  wherecondition:=stlinkcriteria,  View:=acViewPreview

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 2

Author Comment

by:russell12
ID: 38314686
Ok first post, did not change anything, still showed report not filtered.  Second post, I got this written like this and the weird thing is it actually worked, 1 time then kept getting "Data type mismatch in criteria expression" , 3rd post, getting a "Reserved Error".  I think Steveo is correct, it is a syntax issue, just dont know exactly what is wrong with it.  So if I get a couple of eyes looking at it, someone will help me catch it.  I really appreciate you 3 for helping me!!

  Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "[wkstorenumber]= " & "3268"
   '& Str(Nz(Me![List8], 0))
 DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38314720
russell12,

That is actually exactly what I suggested in the top post (you were missing a comma).

try simplifying it to this:

  Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "[wkstorenumber]= 3268"

 DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Open in new window


Or if wkstorenumber is a text field use this (the above will work for numeric data):

  Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "[wkstorenumber]= '3268'"

 DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Open in new window



If all else fails, post a sample copy of your database so that we can take a look at it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38314724
Also, what is the SQL for the query that your report is based on?  (or is it simply based on a table?)
0
 
LVL 2

Author Closing Comment

by:russell12
ID: 38314742
Ok it was something so simple, the field is set to "text" in db so i forgot the ' ' around 3268.  The code I am using is:

  Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "[wkstorenumber]= " & "'3268'"
 DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Thank you for helping me with this!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38314750
You're welcome!  from your earlier post, it looked like you were trying to include a listbox value (possibly to filter your report based on a user selection from a listbox?)

The syntax for that with a text field would be:


Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "1mothlyreportforstore"
   stLinkCriteria = "[wkstorenumber]=  '" & Me![List8]  & "'"
 DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

809 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