Solved

Filter Report based on Table Value

Posted on 2011-03-01
19
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 500 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

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

726 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