Solved

Filter Report based on Table Value

Posted on 2011-03-01
19
449 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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