Want value of ALL for drop down in form

[joylene6]

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?

 

[Remou]

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

 

[joylene6]

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.

 

[Remou]

After the user selects a location or drug class, what happens?

 

[joylene6]

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.

 

[Remou]

Is the report based on a query? Are you building a query in code? Please post some code.

 

[joylene6]

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;

 

[Remou]

The above query does not seem to have any reference to a location or a class. There is only one reference to a form, and it does not refer to a date range.

Have you seen

http://www.tek-tips.com/faqs.cfm?fid=5497?

 

[joylene6]

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.

 

[Remou]

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]

 

[PHV]

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