Calculation Issues (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:20
Joined
Feb 28, 2001
Messages
26,996
If it is publicly declared in a general module, then perhaps something like this:

Code:
UPDATE tblX SET fldX=RoundUp(fldx) ;

Note that this WILL NOT WORK in a query if declared in a form or report Class module.

"Publicly declared" means that the keyword PUBLIC precedes the word FUNCTION in the declaration of the function. See, for example, my code in post #16.
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
If it is publicly declared in a general module, then perhaps something like this:

Code:
UPDATE tblX SET fldX=RoundUp(fldx) ;

Note that this WILL NOT WORK in a query if declared in a form or report Class module.

"Publicly declared" means that the keyword PUBLIC precedes the word FUNCTION in the declaration of the function. See, for example, my code in post #16.

Disclaimer: Nothing against your suggested function in post 16, however I started testing with Isla's code to see if I could even get it to work, so below is my config.

I'm sure i'm doing something wrong as this is all new territory for me, but I added a Module titled RoundUp, but then read that you shouldn't name the module the same as the function, so the module is named TabletCalc with the following code
Code:
Public Function RoundUp(N) As Integer

    RoundUp = IIf(Int(N) <> N, Int(N) + 1, Int(N))
    
    [Forms]![TabletCalculatorFacilities].[TabletCalculator Subform].[Form]![CurrentTablets] = RoundUp([Forms]![TabletCalculatorFacilities].[TabletCalculator Subform].[Form]![Participants] / [Forms]![TabletCalculatorFacilities]![Tablet Ratio])
               
End Function

In my Update Query, I have the following code:

Code:
UPDATE TabletCalculator
SET CurrentTablets = RoundUp(CurrentTablets)

however when I run the query, I am getting the following error message:

'RoundUp' is not a recognized built-in function name. (#195)
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
The change you made to my function isn't appropriate.
You should have left it as it was!

Firstly, to clarify something Doc wrote, a procedure (Function or Sub) is treated as Public UNLESS it is written specifically as Private.
So Function and Public Function are 'functionally' the same.
(Never thought I'd write Function 3 times in a sentence of 9 words!)

I've attached a very simple app showing how the RoundUp function can be used in a query or in code. Whilst I don't normally save calculated data... I'm doing so in this case purely as an illustration.

The function is in Module1
Table1 has number fields Input1 and Input2 together with an Output field.
where Output will be calculated as RoundUp(Input1/Input2)

Query1 SQL is:
Code:
UPDATE Table1 SET Table1.[Output] = RoundUp([Input1]/[Input2]);
That will update all records

Or use the form, enter values for Input1 & Input2 and click the button to update the Output for that record only. The code is
Code:
Private Sub cmdUpdate_Click()
        Me.Output = RoundUp([Input1] / [Input2])
End Sub

NOTE: I could of course have set Output as a calculated field and set its value = RoundUp([Input1] / [Input2])

Hope that helps
 

Attachments

  • NearImpossibleUpdate.accdb
    416 KB · Views: 114

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
I've attached a sample database with instructions of what i'm trying to accomplish.

If you can think of an easier way to accomplish it, please advise.

My actual DB will use linked tables.

Thank you


EDIT: Looks like we were working on a reply about the same time, i'll check your sample

thanks again !!
 

Attachments

  • TabletCount.accdb
    980 KB · Views: 96
Last edited:

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
The change you made to my function isn't appropriate.
You should have left it as it was!

Firstly, to clarify something Doc wrote, a procedure (Function or Sub) is treated as Public UNLESS it is written specifically as Private.
So Function and Public Function are 'functionally' the same.
(Never thought I'd write Function 3 times in a sentence of 9 words!)

I've attached a very simple app showing how the RoundUp function can be used in a query or in code. Whilst I don't normally save calculated data... I'm doing so in this case purely as an illustration.

The function is in Module1
Table1 has number fields Input1 and Input2 together with an Output field.
where Output will be calculated as RoundUp(Input1/Input2)

Query1 SQL is:
Code:
UPDATE Table1 SET Table1.[Output] = RoundUp([Input1]/[Input2]);
That will update all records

Or use the form, enter values for Input1 & Input2 and click the button to update the Output for that record only. The code is
Code:
Private Sub cmdUpdate_Click()
        Me.Output = RoundUp([Input1] / [Input2])
End Sub

NOTE: I could of course have set Output as a calculated field and set its value = RoundUp([Input1] / [Input2])

Hope that helps



Think I get it now, I was missing how the calculation worked out which is why I added it to the function, the calculation is in the () of the RoundUp Function. RoundUp([Input1] / [Input2])

In my case Input 1 will be from table 2 and Input 2 will be from table 1, how can I set that up?
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
Think I get it now, I was missing how the calculation worked out which is why I added it to the function, the calculation is in the () of the RoundUp Function. RoundUp([Input1] / [Input2])

In my case Input 1 will be from table 2 and Input 2 will be from table 1, how can I set that up?

By which method? As a query or in the form?
Are you saving the rounded up value or just using it for display?

Probably you need to create a query linking tables 1 & 2 then use that either as the basis for your update query or in your form code
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
By which method? As a query or in the form?
Are you saving the rounded up value or just using it for display?

Probably you need to create a query linking tables 1 & 2 then use that either as the basis for your update query or in your form code

If you look at post 24, I've attached a sample of what i'm trying to accomplish.

As there could be multiple facilities being setup at the same time, I need to be able to display the results per facility, either stored or calculated and ultimately printed in a report.


I am open to any suggestions if you think I should be heading a different route.
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
I'm going offline for a few hours but hopefully someone else will have a look in the meantime.
But perhaps you could clarify the answers to my questions to save some time
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
I'm going offline for a few hours but hopefully someone else will have a look in the meantime.
But perhaps you could clarify the answers to my questions to save some time

No problem and thanks again for everything.

My query will look like the following as the tablet ratio could be different per facility.

Code:
UPDATE TabletCalculator 
SET CurrentTablets = RoundUp(Participants/[Tablet Ratio])
WHERE TabletCalculator.FacilityTabletID=TabletCalculatorFacilities.FacilityTabletID;

Participants is in the TabletCalculator table and [Tablet Ratio] is in the TabletCalculatorFacilities table, both joined on the FacilityTabletID so i'll have to lookup how to Join tables as I've never done that in a SQL query.

Currently this works, when I run the query, but I have to manually enter the Table Ratio and FacilityTabletID



As far as saving or just displaying the results, I guess I should ask what would be the best option as the result from this calculation will be used as a base in 3 other calculations

Once I get the number of Tablets, that number will be used to determine the number of Charge Bases, and that number will determine the number and type of mounting hardware I need.

In the end, I will print a report with all the required numbers
 

Attachments

  • TabletCount.accdb
    980 KB · Views: 99
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
You can link the tables either in the query design window or in SQL view
Try this on a copy of your database. Its a modified version of your TabletCount update query
Code:
UPDATE TabletCalculatorFacilities 
INNER JOIN TabletCalculator ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID 
SET TabletCalculator.CurrentTablets = RoundUp(Participants/[Tablet Ratio]), 
TabletCalculator.CurrentBases = RoundUp(CurrentTablets/5)
WHERE (((TabletCalculator.FacilityTabletID)=[TabletCalculatorFacilities].[FacilityTabletID]));

The query will run and update the 12 records in that table without any user input.
Does it do what you want?
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
You can link the tables either in the query design window or in SQL view
Try this on a copy of your database. Its a modified version of your TabletCount update query
Code:
UPDATE TabletCalculatorFacilities 
INNER JOIN TabletCalculator ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID 
SET TabletCalculator.CurrentTablets = RoundUp(Participants/[Tablet Ratio]), 
TabletCalculator.CurrentBases = RoundUp(CurrentTablets/5)
WHERE (((TabletCalculator.FacilityTabletID)=[TabletCalculatorFacilities].[FacilityTabletID]));

The query will run and update the 12 records in that table without any user input.
Does it do what you want?

Yes and No, I have to run it twice in order for the CurrentBases to update.
I split into 2 separate queries, one for the TabletCount and then one for the Base Count and all is working as desired.

Thanks again !!!
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
Sorry about that. How about this?

Code:
UPDATE TabletCalculatorFacilities INNER JOIN TabletCalculator
 ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID
 SET TabletCalculator.CurrentTablets = RoundUp(Participants/[Tablet Ratio]), 
TabletCalculator.CurrentBases = RoundUp(Participants/5*[Tablet Ratio])
WHERE (((TabletCalculator.FacilityTabletID)=[TabletCalculatorFacilities].[FacilityTabletID]));
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
Sorry about that. How about this?

Code:
UPDATE TabletCalculatorFacilities INNER JOIN TabletCalculator
 ON TabletCalculatorFacilities.FacilityTabletID = TabletCalculator.FacilityTabletID
 SET TabletCalculator.CurrentTablets = RoundUp(Participants/[Tablet Ratio]), 
TabletCalculator.CurrentBases = RoundUp(Participants/5*[Tablet Ratio])
WHERE (((TabletCalculator.FacilityTabletID)=[TabletCalculatorFacilities].[FacilityTabletID]));

Works great, but the base calculations weren't correct, i've corrected the formula and all is good.

You are a rockstar !!!

Thanks again for your help
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
Try adding the line Me.Recalc after running that code
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
Try adding the line Me.Recalc after running that code

Yup, I got it work with me.refresh.

One last thing I didn't think about is how to handle a null value.

I set the Tablet Ratio and Participants After updates to run the query.

I deleted the value out of the Visitors box (Participants) and it errored out saying invalid use of Null and brought me to the RoundUp Function.
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
Yup, I got it work with me.refresh.

One last thing I didn't think about is how to handle a null value.

I set the Tablet Ratio and Participants After updates to run the query.

I deleted the value out of the Visitors box (Participants) and it errored out saying invalid use of Null and brought me to the RoundUp Function.

I just put an On Error Resume Next at the start of the Function and all is good as it Zeros out the calculated fields.

Thanks again !!
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
Me.Recalc should have worked

On Error Resume Next is usually a poor solution. It just masks the problem

Instead I would use the Nz function to handle nulls then Roundup.
For example Roundup(Nz(Participants/[Tablet Ratio],0))
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
Me.Recalc should have worked

On Error Resume Next is usually a poor solution. It just masks the problem

Instead I would use the Nz function to handle nulls then Roundup.
For example Roundup(Nz(Participants/[Tablet Ratio],0))

Done and Done, thanks again !!
 

isladogs

MVP / VIP
Local time
Today, 09:20
Joined
Jan 14, 2017
Messages
18,186
You're welcome. Can this thread now be marked as solved?
 

NearImpossible

Registered User.
Local time
Today, 04:20
Joined
Jul 12, 2019
Messages
225
You're welcome. Can this thread now be marked as solved?

Just have a small quirk due to the macro updating fields, whenever I click in a new field on the same record to start typing, I get the message that someone has changed the record, ....., so I have to click OK and then click back in that field before I can type.

but as my question was answered, this can be marked as resolved.

thanks again for your help
Kevin
 

Users who are viewing this thread

Top Bottom