Roundup Function

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"));
 
T

Tom Wickerath

Hi Nona,
Can I use this function within a SQL stmt?

You should be able to do this. Have you tried yet? What was the result?

Also, please see my previous reply
http://www.microsoft.com/office/com...cess&mid=cbf04699-4eac-46d1-9d76-5c969b1f88f2


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Nona said:
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"));
 
N

Nona

I looked at and pondered over the sequel statement but I just don't know how
to insert the roundup function. And I thank you for your response with the
websites. However, it's the same - I don't know how to get that code into a
query. I am not new to Access, but I am an absolute novice with writing code.
 
J

John W. Vinson

I looked at and pondered over the sequel statement but I just don't know how
to insert the roundup function.

To put any function call into a query, simply open the query in design view in
the query grid; find a vacant Field cell; and type a name for the field and
the function:

NewFieldName: RoundUp([fieldname], 0)
 
N

Nona

John, Thanks for your patience. I did that, but got this error message:
"Undefined Funcation 'RoundUp' in expression. Here is the complete statement
with my insertion:

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, RoundUp([WkAuths],0) AS WKAuths2, 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"));



--
Nona


John W. Vinson said:
I looked at and pondered over the sequel statement but I just don't know how
to insert the roundup function.

To put any function call into a query, simply open the query in design view in
the query grid; find a vacant Field cell; and type a name for the field and
the function:

NewFieldName: RoundUp([fieldname], 0)
 
J

John W. Vinson

John, Thanks for your patience. I did that, but got this error message:
"Undefined Funcation 'RoundUp' in expression. Here is the complete statement
with my insertion:

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, RoundUp([WkAuths],0) AS WKAuths2, 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"));

Did you look up the references? See
http://support.microsoft.com/kb/209996

Copy and paste the code into a new module, and call it from your query. I'm
busy fixing dinner so can't give detailed instructions but will check back
tomorrow afternoon.
 
T

Tom Wickerath

Hi Nona,

It turns out that Roundup is a valid Excel function, but not a valid
function to use within Access. That's why you received the Undefined Function
error. You could create your own custom Roundup function, although I think
I'd name it something different, so that there could never be the possibility
of a conflict with the Excel function (for example, if you later set a
checked reference to the Excel Object Library).

Round is a valid VBA function, so try changing your current reference to
Roundup to simply round, as in: Round([WkAuths],0) AS WKAuths2

If that does not give you the desired result, then you may want to
experiment with the methods in KB 209996
(http://support.microsoft.com/kb/209996), which do involve creating a custom
function in a new module.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Nona said:
John, Thanks for your patience. I did that, but got this error message:
"Undefined Funcation 'RoundUp' in expression. Here is the complete statement
with my insertion:

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, RoundUp([WkAuths],0) AS WKAuths2, 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"));
 
N

Nona

This worked beautifully! Thank you very much!

Just one other question, and I think it will probably required a new module
as you suggested, only I think that's beyond my capability.

Is there a way (instead of using the zero in the roundup funciton) to round
all the fractions to the next whole number? For instance, I'd like a 3.3 to
round to 4.

Thanks again....this is a huge help.


--
Nona


Tom Wickerath said:
Hi Nona,

It turns out that Roundup is a valid Excel function, but not a valid
function to use within Access. That's why you received the Undefined Function
error. You could create your own custom Roundup function, although I think
I'd name it something different, so that there could never be the possibility
of a conflict with the Excel function (for example, if you later set a
checked reference to the Excel Object Library).

Round is a valid VBA function, so try changing your current reference to
Roundup to simply round, as in: Round([WkAuths],0) AS WKAuths2

If that does not give you the desired result, then you may want to
experiment with the methods in KB 209996
(http://support.microsoft.com/kb/209996), which do involve creating a custom
function in a new module.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Nona said:
John, Thanks for your patience. I did that, but got this error message:
"Undefined Funcation 'RoundUp' in expression. Here is the complete statement
with my insertion:

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, RoundUp([WkAuths],0) AS WKAuths2, 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"));
 
T

Tom Wickerath

Hi Nona,

I believe the required rounding function is included in KB KB 209996:
http://support.microsoft.com/kb/209996

and, yes, it will require that your query call a public function in a new
module. However, I don't think that it is necessarily above your
capabilities; all you need do is use the copy and paste technique from the KB
article to paste the code into a new module. Then, you just need to call this
function correctly, but they show an example of doing that in this article.
Give it a try. We're here to help if you have any trouble getting it to work
correctly.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

John W. Vinson

Is there a way (instead of using the zero in the roundup funciton) to round
all the fractions to the next whole number? For instance, I'd like a 3.3 to
round to 4.

There's a cute trick that depends on the way the Int() function treats
negative numbers - Int(3.3) becomes 3, but Int(-3.3) becomes -4. So

XRoundedUp: -Int(-[X])

will work for you. If the field X is 3.3, XRoundedUp will be displayed as 4.
 
N

Nona

Wow! This works like a charm! Exactly what I need!
Thank you, Thank you, Thank you!

--
Nona


John W. Vinson said:
Is there a way (instead of using the zero in the roundup funciton) to round
all the fractions to the next whole number? For instance, I'd like a 3.3 to
round to 4.

There's a cute trick that depends on the way the Int() function treats
negative numbers - Int(3.3) becomes 3, but Int(-3.3) becomes -4. So

XRoundedUp: -Int(-[X])

will work for you. If the field X is 3.3, XRoundedUp will be displayed as 4.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top