Thanks for responding so quickly, Steve. I need a little more help with this.
I understand the criteria but how do I put the Where stmt in the query so
that the user can enter the value "H0036" and retrieve all those with
different suffixes?
Am I asking too much?
Below is the SQL:
SELECT tblAuths.AuthNo, tblAuths.ConsumerNo2, qryConsumers.CLN,
qryConsumers.CFN, tblAuths.AuthStart, tblAuths.AuthEnd,
DateDiff("d",[DateToday],[AuthEnd]) AS DaysRemain, [DaysRemain]/7 AS NoWks,
-Int(-[NoWks]) AS NoWksRdUp, Date() AS DateToday,
IIf(Nz([HrsRemain],0)>0,[HrsRemain]/[NoWksRdUp],0) AS WkAuths,
IIf([WkAuths]<8,[WkAuths]) AS MaxWkHrs1, IIf([WkAuths]>8,"8.00",[WkAuths]) AS
MaxWkHrs2, -Int(-[MaxWkHrs2]) AS MaxWkHrsRdUp, tblAuths.AuthUnitsApproved,
[AuthUnitsApproved]-[UnitsUsed] AS UnitsRemain, ([UnitsRemain]/4) AS
HrsRemain, qryConsumers.TLFN, qryConsumers.TLLN, tblAuths.AuthSubmitted,
tblAuths.AuthReceived, tblAuths.UnitsUsed, tblAuths.ServiceCode,
tblAuths.CostCtr, tblAuths.AuthsStatus, tblAuths.Source,
IIf([Source]="Medicaid",1,0) AS Medicaid, IIf([Source]="Health Choice",1,0)
AS HChoice, IIf([Source]="Value Options",1,0) AS VOptions,
IIf([Source]="IPRS",1,0) AS IPRS, IIf([Source]="PBH",1,0) AS PBH,
IIf([Source]="CAP",1,0) AS CAP, qryConsumers.ClientStatus, tblAuths.Comment,
tblAuths.TarNo, tblAuths.UnitsLeft,
DateDiff("d",[AuthSubmitted],[AuthReceived]) AS TurnADays,
IIf(Nz([AuthUnitsApproved],0)>0,[UnitsUsed]/[AuthUnitsApproved],0) AS BurnRate
FROM qryConsumers INNER JOIN tblAuths ON qryConsumers.ConsumerNo1 =
tblAuths.ConsumerNo2
GROUP BY tblAuths.AuthNo, tblAuths.ConsumerNo2, qryConsumers.CLN,
qryConsumers.CFN, tblAuths.AuthStart, tblAuths.AuthEnd, Date(),
tblAuths.AuthUnitsApproved, qryConsumers.TLFN, qryConsumers.TLLN,
tblAuths.AuthSubmitted, tblAuths.AuthReceived, tblAuths.UnitsUsed,
tblAuths.ServiceCode, tblAuths.CostCtr, tblAuths.AuthsStatus,
tblAuths.Source, qryConsumers.ClientStatus, tblAuths.Comment, tblAuths.TarNo,
tblAuths.UnitsLeft
HAVING (((tblAuths.AuthEnd)>Date()) AND ((tblAuths.ServiceCode)=[Enter
Service Code]) AND ((tblAuths.AuthsStatus)="Pending") AND
((qryConsumers.ClientStatus)="Active")) OR (((tblAuths.AuthEnd)>Date()) AND
((tblAuths.ServiceCode)=[Enter Service Code]) AND
((tblAuths.AuthsStatus)="Approved") AND
((qryConsumers.ClientStatus)="Active"));
Many thanks!
--
Nona
S.Clark said:
In the query, use the wildcard operator *.
...WHERE [service code] Like [Enter Service Code] & "*"
--
Steve Clark, Former Access MVP
SharePoint pwned
Washington, DC
Nona said:
Is there a way for a user to search a database to find related values in the
same field?
I have a report in which the query asks what service code (out of a list of
about 100) the user wants included in the report. There are several related
codes, such as H0036 HA, H0036 3 and H0036 P. Is there a way they could enter
H0036 and get all the records with the H0036 but with different suffixes?
I've tried entering H0036 HA Or H0036 3, but it doesn't find any records at
all. I tried putting those in quotation marks, but that didn't work either.
Surely there is a way to do this!
Thanks for any help!
