[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Access 2003 - Criteria for a report - choose all

I have an Access 2003 database - I have report based of a query. In the queyr I criteria to choose what is wanted when opened. [choose optoisn] and the query pulls out those records. Is there a way to choose all like when you use the *
0
Pdeters
Asked:
Pdeters
  • 8
  • 5
  • 2
2 Solutions
 
ldunscombeCommented:
Can you post your sql and some sample data ?
0
 
PdetersAuthor Commented:
HEre is the quey cut down - it is a basic query
WHat I want is to be able to Choose the "Plan Name" from the Patient Table each time a report is run.
I can do that now by typing in the plan - "Cobra" but I want to be able to choose all the records and not just a certain plan

SELECT [Billing Item Table].BillingItemID, [Contract Table].ContractID, [Patient Table].PatientID, [Contract Table].CompanyName, [Patient Table].InsuredPlanName
FROM ([Contract Table] RIGHT JOIN [Patient Table] ON [Contract Table].ContractID = [Patient Table].ContractID) RIGHT JOIN [Billing Item Table] ON [Patient Table].PatientID = [Billing Item Table].PatientID
WHERE ((([Patient Table].InsuredPlanName)=[Plan Name]));
0
 
ldunscombeCommented:
try this
SELECT [Billing Item Table].BillingItemID, [Contract Table].ContractID, [Patient Table].PatientID, [Contract Table].CompanyName, [Patient Table].InsuredPlanName
FROM ([Contract Table] RIGHT JOIN [Patient Table] ON [Contract Table].ContractID = [Patient Table].ContractID) RIGHT JOIN [Billing Item Table] ON [Patient Table].PatientID = [Billing Item Table].PatientID
WHERE ((([Patient Table].InsuredPlanName) Like [Plan Name]));

By changing the = to Like in the where clause you should be able to use the * character to select all records
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
harfangCommented:
Leigh's SQL works under the assumption that InsuredPlanName is never Null. To select Null plans, you'd need something like this:

    WHERE [Plan Name] Is Null Or [Patient Table].InsuredPlanName = [Plan Name]

Not entering any parameter creates a Null parameter.
(°v°)
0
 
PdetersAuthor Commented:
If I select all I want to be able to select the null ones also. If I put in

    WHERE [Plan Name] Is Null Or [Patient Table].InsuredPlanName = [Plan Name]

inplace of
    WHERE ((([Patient Table].InsuredPlanName) Like [Plan Name]));

I am able to choose the blank ones if I put nothing in the criteria. But if I type in criteria I get the "Plan Name" twice to input criteria.

I am looking to get just one box that asks for criteria and be able to get all including the blanks or just what I type in
0
 
harfangCommented:
If you get it twice, it means it's spelled differently. Just to make sure, add a PARAMETERS section: menu (Query | Parameters...):

    PARAMETERS [Plan Name] Text;
    SELECT ...
    WHERE [Plan Name] Is Null Or [Patient Table].InsuredPlanName = [Plan Name]

Of course, if your last comment it a plain cut-and-paste from your actual query, I don't see the spelling difference... but I've never seen Access asking for the same parameter twice!

(°v°)
0
 
PdetersAuthor Commented:
It was a plain cut and paste. If I put in the one I only get ! parameter - when I put in the hone from harfang I get 2 -

Where would I put in the PARAMETERS section?
0
 
PdetersAuthor Commented:
THis is what I have now

PARAMETERS [Plan Name] Text ( 255 );
SELECT [Billing Item Table].BillingItemID, [Contract Table].ContractID, [Patient Table].PatientID, [Contract Table].CompanyName, [Patient Table].InsuredPlanName
FROM ([Contract Table] RIGHT JOIN [Patient Table] ON [Contract Table].ContractID = [Patient Table].ContractID) RIGHT JOIN [Billing Item Table] ON [Patient Table].PatientID = [Billing Item Table].PatientID
WHERE ((([Plan Name]) Is Null)) OR ((([Patient Table].InsuredPlanName)=[Plan Name]));
0
 
PdetersAuthor Commented:
It works except it asks for the PARAMETERS twice.??
They both have to be the same to pull up what I want. Blank for all or what ever I put in but it has to be put in twice?
0
 
harfangCommented:
Hmm. This is just plain wrong. I can't get anything similar. How do you manage to have Access ask for the same parameter twice?

Also, you say that if you close the first box, you don't get the second, is that right?

If your query is called qryTest, what do you get with:

    ? CurrentDb.QueryDefs("qryTest").Parameters.Count

Also 2? If so, what are:

    ? CurrentDb.QueryDefs("qryTest").Parameters(0).Name
    ? CurrentDb.QueryDefs("qryTest").Parameters(1).Name

Just fishing, really. I have no idea what's going on.
(°v°)
0
 
PdetersAuthor Commented:
It is really weird. I did a cut and paste so you seen exacltly what I had.
I get both boxes but if I don't answer them the same I get a blank query.

I created a new query and put the same sql statement (a cut and paste) and it now works. Too weird.

It is working!!!
0
 
PdetersAuthor Commented:
Thanks for all the help!!
0
 
harfangCommented:
Well, I managed to get this behaviour by replacing one of the spaces with a required space, Chr(160) instead of Chr(32). But that is not what you have...
(°v°)
0
 
harfangCommented:
LOL. Glad it worked! -- (^v°)
0
 
PdetersAuthor Commented:
It was just a quirk I guess. THanks for the help
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now