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
Solved

Filter Report based on Table Value

Posted on 2011-03-01
19
450 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 84
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

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.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

837 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