• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Filter Report based on Table Value

Good morning,


I have built a report based on table data and i would like the report that i built to display a specific PO# when the form is first opened.

A pop up window would ask the user what PO they would like displayed.

Can anyone help me out with that filtering?

Thanks in advance!
0
hcp27
Asked:
hcp27
  • 9
  • 8
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Base your form on a Query, and include a Parameter in the query. This will cause the Query to prompt the user to enter the value.

For example, if you base your report on a query like this:

SELECT PONum, PODate FROM tblPO WHERE PONum=[Enter PO Number: ]

Access will show the user an input box with the message "Enter PO Number: ". When the user enters the value, Access will attempt to show the user those records that match the value entered.
0
 
hcp27Author Commented:
Even if its a report?
0
 
hcp27Author Commented:
Ok-

I wrote what you said but now its asking for user inputed values for all the feilds on my report.

How do i only get it to ask for just PO#
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Nick67Commented:
You could get it done in the report open event too, with an input box
Private Sub Report_Open(Cancel As Integer)

Dim strCriteria As String
Dim myinput As String

myinput = InputBox("Enter the PO you're looking for")
strCriteria = "[PO#] = '" & myinput & "'"
'the assumption here is that PO# is alphanumeric
'the single quotes won't be needful for strictly numeric data
'comment and uncomment the appropriate line
'strCriteria = "[PO#] = " & myinput 
me.Filter = strCriteria
me.FilterOn = true

end sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Please post the EXACT SQL you're using on the Report.
0
 
hcp27Author Commented:
SELECT POLOG.[TCTPO#:]
FROM POLOG
WHERE (((POLOG.[TCTPO#:])=[Enter PO Number:]));
0
 
Nick67Commented:
Whether TCTPO# is numeric or alphanumeric matters a great deal.
If it is alphanumeric it has to be wrapped in quotation marks
Chr(34) is a doublequote "

SELECT POLOG.[TCTPO#:]
FROM POLOG
WHERE (((POLOG.[TCTPO#:])=chr(34) & [Enter PO Number: & chr(34)]));
0
 
hcp27Author Commented:
wtf
This is what i am getting?

What am i doign wrong?

I'm sooooo close!!!
0
 
Nick67Commented:
You've put in your parameters in a way we didn't anticipate...I think.
Post a shot of the report's prpoerties.  I want to see the RecordSource.
It should be a query.
If it is open the query in design view, and then change it to SQL view, and post the results.
10 to 1 you've got a line that starts PARAMETERS.

That isn't what you need.
Also, tell me from the table design what data type TCTPO# is
0
 
hcp27Author Commented:
Please see attatched screen Shots.


Thanks for all your help Nick. This is like one of the last steps of this project.

Thanks!
 Report SQL Code Table
0
 
Nick67Commented:
I gotta admit, when I see your field naming conventions I shudder.
Those spaces and colons make for a maintainability nightmare.
If you aren't in production yet, it would be worthwhile to look up SQL Server naming conventions and rework the field names.
If you ever go to upsize this to SQL Server -3 or 5 years out- you'll be glad you did.

That aside.
TCTPO# is numeric.  That means your [Enter a PO Number] does NOT need--and must not have -- doublequotes around it.  Your bracketing is also wonky.

The thing about naming conventions -- if TCTPO#: was instead TCTPONumber, which is compliant, the square brackets [ ] would not be necessary. Access puts in all the (( )) brackets.  When you are troubleshooting, delete the auto-generated ones.  Access will recreate them.  Try this as your SQL statement.  Access will add bracketing and the semi-colon after
Select POLOG.[TCTPO#:]
from POLOG
Where POLOG.[TCTPO#:] = & [Enter PO Number]

Open in new window

0
 
Nick67Commented:
Nine for ten, there is something in the PARAMETERS hiding in the query.  But we'll blow that up after.
You can create a new query, name it qryTCTPOnumber and paste my SQL into it.  Set the report's Record Source to be that query from the dropdown
0
 
hcp27Author Commented:
Thanks for your help Nick.

I am new to acess SQL programming and any help is much appreciated. Thanks so much for all your time and effort.

I did what you said and it is still asking me to input ALL the values on the report. I dont understand.


All i want it to do is ask the user what po# the user wants to view, have the user type that value in and display the results on the report.

I guess i dont know what i am doing wrong to make this work?

i inputed your code and saved it as the qry you suggested.

I guess I have hopeless. lol.


thanks Again
0
 
Nick67Commented:
No biggie.
First thing first.
Does qryTCTPOnumber work correctly when you open just the query?
It should prompt you for input and then open a datasheet with the right data.
Does that work?
0
 
Nick67Commented:
D'Oh!

Looking at your report in design view, you see all those little green triangles?
Those 9 for 10 are indicating that those Control Sources don't exist in the report's Record Source

D'Oh!

Select POLOG.[TCTPO#:]
from POLOG
Where POLOG.[TCTPO#:] = & [Enter PO Number]

returns just a single field--TCTPO#:

But that's the SQL you posted.
What was the report based on BEFORE we began?
A table?
POLOG?

If so then the query shoud be

Select * from POLOG
Where POLOG.[TCTPO#:] = & [Enter PO Number]
0
 
hcp27Author Commented:
Dear Nick. You are my hero!

How can i ever be AS smart as you?!?!?! Will i ever?!?!
0
 
Nick67Commented:
Probably.  Google is a great leveler, and like Newton said, we stand on the shoulders of giants

Allen Browne
http://allenbrowne.com

Stephen Lebans
http://www.lebans.com/

Dev Ashish
http://www.mvps.org/access/

If you are starting out you absolutely need to look at
http://www.mvps.org/access/tencommandments.htm

There are other giants out there -- but these dudes definitely got me going the right direction
Glad you go it working
0
 
hcp27Author Commented:
Nick! YOU ROCK!!!!
0
 
Nick67Commented:
And if your db isn't in production yet, to obey commandment #3 check out
http://support.microsoft.com/kb/173738

And to avoid the bombshell of naming a field Date, and then forever after having to put [Date] in all code, queries, and parameters, check out
http://support.microsoft.com/kb/286335

And never put an # in a fieldname, or you are forever doomed to put [some#] in code, queries and parameters, instead of just SomeNumber.

Have a good weekend
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now