Conditional criteria in a query

[Nona]

Can I put an If Then condition in a design query?

In a query I have a field for [WkAuth] and a condition in the query IIF
[WkAuth]< 8, [WkAuth], "8". (The [WKAuth] represents the maximum number of
hours scheduled for the week.)

For most services, the 8 hours max works. However I've been asked to add a
modification to this query for a certain service code. If a certain service
code exists, then the max hours scheduled should be 32 instead of 8.

I don't think the IIf statement will work for this. If not, then how can I
handle this. I am not skilled in writing code, which may be what is needed.

Can someone please help? I'm using Access 2000, and my query statement
follows:

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.AuthStart)<Date() Or (tblAuths.AuthStart)=Date()) AND
((tblAuths.AuthEnd)>Date()) AND ((tblAuths.ServiceCode) Like [Enter Service
Code] & "*") AND ((tblAuths.AuthsStatus)="Approved") AND
((qryConsumers.ClientStatus)="Active"));

 

[Clifford Bass]

Hi Nona,

Just use more IIf()s:

IIf([ServiceCode] = "the value", IIf([WkAuth] < 32, [WkAuth], 32),
IIf([WkAuth] < 8, [WkAuth], 8))

Ideally, you should have those values entered into the service codes
table and join your query to the service codes table so that you can do
something like this:

IIf([WkAuth] < [MaxServiceHours], [WkAuth], [MaxServiceHours])

where the MaxServiceHours would be the column from the service codes table.

Hope that helps,

Clifford Bass

Can I put an If Then condition in a design query?

In a query I have a field for [WkAuth] and a condition in the query IIF
[WkAuth]< 8, [WkAuth], "8". (The [WKAuth] represents the maximum number of
hours scheduled for the week.)

For most services, the 8 hours max works. However I've been asked to add a
modification to this query for a certain service code. If a certain service
code exists, then the max hours scheduled should be 32 instead of 8.

I don't think the IIf statement will work for this. If not, then how can I
handle this. I am not skilled in writing code, which may be what is needed.

Can someone please help? I'm using Access 2000, and my query statement
follows:

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.AuthStart)<Date() Or (tblAuths.AuthStart)=Date()) AND
((tblAuths.AuthEnd)>Date()) AND ((tblAuths.ServiceCode) Like [Enter Service
Code] & "*") AND ((tblAuths.AuthsStatus)="Approved") AND
((qryConsumers.ClientStatus)="Active"));

 

[Clifford Bass]

Hi Nona,

Adjust it to this to deal with both of the issues:

MaxWkHrs2: IIf(Left([ServiceCode], 5) = "H2015", IIf([WkAuths] < 32,
[WkAuths],
32),IIf([WkAuths] < 8, [WkAuths], 8))

Note that you had a closing quote (â€) at the end of the string; changed
to a regular quote (").

Clifford Bass

P.S. Jeff - No problem

 

[Nona]

Wow! Success! Thanks so much, Clifford. I would never have figured all that
out on my own. I am also glad to learn about using Left to call up the codes.
Thanks so very very much.
--
Nona

Hi Nona,

Adjust it to this to deal with both of the issues:

MaxWkHrs2: IIf(Left([ServiceCode], 5) = "H2015", IIf([WkAuths] < 32,
[WkAuths],
32),IIf([WkAuths] < 8, [WkAuths], 8))

Note that you had a closing quote (â€) at the end of the string; changed
to a regular quote (").

Clifford Bass

P.S. Jeff - No problem

oooh, Important distinction. Thank you, Jeff.

I think I fixed that, but I'm still doing something wrong, but maybe not.
I'm still getting an error message for an invalid string.

MaxWkHrs2: IIf([ServiceCode] = "H2015 HT Pâ€, IIf([WkAuths] < 32, [WkAuths],
32),IIf([WkAuths] < 8, [WkAuths], 8))

 

[Clifford Bass]

Hi Nona,

Great to hear that! You are welcome.

Clifford Bass