Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Want value of ALL for drop down in form

Status
Not open for further replies.

joylene6

Technical User
Jan 14, 2008
6
US
I am building a form to look for classes of drugs in a query.

I have 2 drop downs. I have manually added the classes to one dropdown. I have manually added locations to the other.

I have added a option of ALL in both dropdowns.

I think the easiest way would be to write a command in the query to have ALL = location A, location B ect.

Is this how to do it?


 
Probably not. What do you intend doing with this? To illustrate a query:

Location=LocationA

Select * From tblTable Where Location = cboLocation

Location=All

Select * From tblTable
 
Thank you for responding.

I want users to be able to select one or all drug classes

and, I want them to pick one or all locations.


There are hundreds of drug classes, so I put a drop down in the form to limit to the ones that are relevant for the report's use.

 
After the user selects a location or drug class, what happens?
 
They put a date range in and click a "RUN REPORT" button.

A report would then appear with the drug class information for the locations they picked from the drop down.

 
Is the report based on a query? Are you building a query in code? Please post some code.
 
Here is the SQL for my query

SELECT TOP 100 PDM.LABEL_NAME AS [Generic Name], PDM.BRAND_NAME AS [Brand Name], Nz([THERA_CODE_FMT],"92:00.00") AS [AHFS Classification], Nz([THERA_DESC],"MISCELLANEOUS THERAPEUTIC AGENTS\1") AS [Therapeutic Category], PDM.ROUTE, PHM_CURES.TRANS_DATE, PHM_CURES.PATIENT_LAST_NAME, PHM_CURES.PATIENT_FIRST_NAME, PHM_CURES.PTNRSTA
FROM (PDM LEFT JOIN PTC ON PDM.THER_CODE_1 = PTC.THERA_CODE_FMT) INNER JOIN PHM_CURES ON PDM.DRUG_CODE = PHM_CURES.DRUG_CODE
WHERE (((Nz([THERA_CODE_FMT],"92:00.00")) Like "08:*") AND ((PHM_CURES.PTNRSTA)=[Forms]![JL anti-infective]![Nstation]) AND ((PDM.FORMULARY_STATUS)="F") AND ((PDM.ACTIVE_IND)="YES"))
ORDER BY PDM.LABEL_NAME;
 
The location is the Nursestation and the class is the AHFS classification.

I do not have the date range built - I am just using transaction date right now.
 
Something on these lines may suit:

[tt]<...>
WHERE
(((Nz([THERA_CODE_FMT],"92:00.00")) Like "08:*")
AND
((PHM_CURES.PTNRSTA) Like IIf([Forms]![JL anti-infective]![Nstation]="all","*",[Forms]![JL anti-infective]![Nstation]))
AND
((PDM.FORMULARY_STATUS)="F")
AND
((PDM.ACTIVE_IND)="YES"))
ORDER BY PDM.LABEL_NAME;[/tt]

 
...
WHERE Nz([THERA_CODE_FMT],"92:00.00") Like "08:*"
AND (PHM_CURES.PTNRSTA=[Forms]![JL anti-infective]![Nstation] OR [Forms]![JL anti-infective]![Nstation]='ALL')
AND PDM.FORMULARY_STATUS="F" AND PDM.ACTIVE_IND="YES"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top