How to set up Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have my database all built the only problem I have is how I would set up a
Query for a Report. I have a Plan Name that can be sold to more then one
person and can be purchased 3 different ways, how would I set up the query so
that I want to see the results for a specific plan and how it was purchased?

Example: Design was sold by Web site, how would I check just the website
orders from that design. Also if I want to see all of the plans at the same
time in my report how would I set that up in my Query?


Thanks
 
Your report's record source can be filtered using several methods. Allen
Browne has one example of filtering by date at
http://www.allenbrowne.com/casu-08.html. You shouldn't have too much
difficulty changing his date filtering to some other type of field.

If you need more help, you should come back with more specific information
such as field names and data types.
 
Ok I have a Design Name and I want to set up a query for when the user opens
up the report I would like for the user to enter either the design name and
how it was purchased. The Parameter Value will allow the user to enter the
info when the report opens, in other words: When they open up the report and
just want to see the design name along with the way it was purchased. Also,
if they just want to see All the designs and the way they were purchased.

Thanks in Advance.
 
I prefer using the method as described by Allen. The user makes selections or
enters values into a form and then clicks a button to open the report based
on the filter.

Did you try this method? Did you have issues?
 
I tried using this method but because it's not using text I can not figure it
out. I tried entering Is Null under the or and I get duplicates showing after
running the Query. I'm only trying to have the user select the Plan Design
along with the way it was purchased only it they want a specific Plan, if not
I would like it to open all Plan designs. Do you know how I would be able to
do this using a parameter query?

Thanks in Advance
 
Do you have some form and control names (used for filtering) as well as
fields and data types from the report's record source that are used for
filtering?
 
Yes I have a form from where the info is coming from I create a query using
that form. As far as filtering, I'm not sure what you are referring to.

Thanks
 
When I asked "Do you have some form and control names..." I wasn't expecting
a yes/no type answer. I was hoping you would share some information so that
someone could help you. By filtering, I mean fields (and their data types)
used for setting the criteria for the records to display in your report.
 
Sorry about that. I have 5 text fields and 7 currency fields I have a combo
box that is for the Purchase Method which is Direct Bill, Web Site, and E
Plans. These are the ways the Designs can be purchased. What I need to happen
is when a user wants to see a particular Design and how it was purchased they
would use the Parameter Query option for this. And also if a user just wants
to see all the Designs they wouldn't have to enter anything in the parameter
it would come up auto.

Thanks Again.
 
You still didn't provide any field names so assuming you have an orders
report in the Northwind sample mdb and you want to display orders based on
the ShipVia field and EmployeeID.

You would create a form with two combo boxes. cboShipVia based on the
Shippers table and cboEmployeeID based on the Employees table.

You would create a report based on the Orders table with no criteria.

Add a command button on the form with code like:

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.cboShipVia) Then
strWhere = strWhere & " And ShipVia = " & Me.cboShipVia
End If
If not IsNull(Me.cboEmployeeID) Then
strWhere = strWhere & " And EmployeeID = " & Me.cboEmployeeID
End If
DoCmd.OpenReport "rptYourRptName" , acPreview, , strWhere
 
Ok, the fields name Plan Name for the Designs, Plan Number, Cost of Plan, Web
Charges, Direct Bill Charge, E Plan,Total, these are the Payment methods.
Then I have a customer name and Date Plan sold.

Thanks
 
Did you try apply my solution to your situation? Did you create a form with a
combo boxes that allow users to select criteria values?
 
Yes I tried and nothing happen when I selected the command button. I'm not
sure if I set it up right. I wasn't sure what to put in the place of If not
IsNull(Me.cboShipVia) and so on.

Thanks
 
"I wasn't sure what to put in the place of If not IsNull(Me.cboShipVia) and
so on" If you were in the Northwind MDB and had created a form with controls
like I suggested, you wouldn't need to replace cboShipVia with anything else
since this would be the name of the combo box.
 
Ok but I'm not in Northwind MDB I have my own database that I'm working in.
I'm not sure really what you're saying here. Can you please help me out with
some more info please? All I'm trying to do is create a Parameter Query to
where I can create a report by selecting either a Plan Name and the way it
was purchased or just show all Plans.

Thanks
 
I don't do parameter prompts. I use controls on forms to enter report
criteria.
Do you have an unbound form that you can add unbound controls to allow users
to enter/select criteria?
If not, create one.
If so, what are the control names?
What fields in your report's record source do these controls relate to?
What are the data types of these fields?
What is your report's name?
 
Back
Top