?
Solved

Filter Report based on Table Value

Posted on 2011-03-01
19
Medium Priority
?
456 Views
Last Modified: 2012-05-11
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
Comment
Question by:hcp27
  • 9
  • 8
  • 2
19 Comments
 
LVL 85
ID: 35010770
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
 

Author Comment

by:hcp27
ID: 35012032
Even if its a report?
0
 

Author Comment

by:hcp27
ID: 35012318
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 26

Expert Comment

by:Nick67
ID: 35023474
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
 
LVL 85
ID: 35027030
Please post the EXACT SQL you're using on the Report.
0
 

Author Comment

by:hcp27
ID: 35029355
SELECT POLOG.[TCTPO#:]
FROM POLOG
WHERE (((POLOG.[TCTPO#:])=[Enter PO Number:]));
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35029395
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
 

Author Comment

by:hcp27
ID: 35029955
wtf
This is what i am getting?

What am i doign wrong?

I'm sooooo close!!!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35031372
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
 

Author Comment

by:hcp27
ID: 35031577
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35032594
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35032613
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
 

Author Comment

by:hcp27
ID: 35038217
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35038692
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35038756
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
 

Author Comment

by:hcp27
ID: 35040196
Dear Nick. You are my hero!

How can i ever be AS smart as you?!?!?! Will i ever?!?!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35040292
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
 

Author Closing Comment

by:hcp27
ID: 35040301
Nick! YOU ROCK!!!!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35040373
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

Featured Post

Independent Software Vendors: 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

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