N
Nona
I want a number field [WKAUTHS] to round up. Access Help says to use the
function ROUNDUP(number,num_digits). Can I use this function within a SQL
stmt? For instance, can I insert it in the following? If not, how should I
use it?
Thanks for your help.
SELECT tblAuths.AuthNo, tblAuths.ConsumerNo2, qryConsumers.CLN,
qryConsumers.CFN, Count(qryConsumers.CLN) AS CountOfCLN,
Count(qryConsumers.CFN) AS CountOfCFN, tblAuths.AuthStart, tblAuths.AuthEnd,
DateDiff("d",[DateToday],[AuthEnd]) AS DaysRemain, [DaysRemain]/7 AS NoWks,
Date() AS DateToday, IIf(Nz([HrsRemain],0)>0,[HrsRemain]/[NoWks],0) AS
WkAuths, 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.AuthsStatus)="Approved" Or
(tblAuths.AuthsStatus)="Pending") AND ((qryConsumers.ClientStatus)="Active"));
function ROUNDUP(number,num_digits). Can I use this function within a SQL
stmt? For instance, can I insert it in the following? If not, how should I
use it?
Thanks for your help.
SELECT tblAuths.AuthNo, tblAuths.ConsumerNo2, qryConsumers.CLN,
qryConsumers.CFN, Count(qryConsumers.CLN) AS CountOfCLN,
Count(qryConsumers.CFN) AS CountOfCFN, tblAuths.AuthStart, tblAuths.AuthEnd,
DateDiff("d",[DateToday],[AuthEnd]) AS DaysRemain, [DaysRemain]/7 AS NoWks,
Date() AS DateToday, IIf(Nz([HrsRemain],0)>0,[HrsRemain]/[NoWks],0) AS
WkAuths, 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.AuthsStatus)="Approved" Or
(tblAuths.AuthsStatus)="Pending") AND ((qryConsumers.ClientStatus)="Active"));