Filter Reports vba

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
LVL 2
russell12Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
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
 
mbizupCommented:
You're missing a comma.

Try this:

 DoCmd.OpenReport stDocName, acViewPreview,, stlinkcriteria
0
 
5teveoCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mbizupCommented:
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
 
russell12Author Commented:
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
 
mbizupCommented:
Also, what is the SQL for the query that your report is based on?  (or is it simply based on a table?)
0
 
russell12Author Commented:
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
 
mbizupCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.