Roundup Function

  • Thread starter Thread starter Nona
  • Start date Start date
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"));
 
Hi Nona,
Can I use this function within a SQL stmt?
Click to expand...

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"));
Click to expand...
 
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.
 
I looked at and pondered over the sequel statement but I just don't know how
to insert the roundup function.
Click to expand...

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)
 
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.
Click to expand...

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)
Click to expand...
 
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"));
Click to expand...

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.
 
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"));
Click to expand...
 
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"));
Click to expand...
Click to expand...
 
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
__________________________________________
 
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.
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...
 

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

Conditional criteria in a query 4

Back
Top