Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

# Access help needed

2 replies to this topic

### #1 stidyup

stidyup

• Members
• 641 posts
• OFFLINE
•
• Gender:Male
• Local time:09:54 AM

Posted 16 August 2005 - 06:24 AM

I'm designing a Access database and I need to do 2 calculations on a form.

The first is to calculate the number of tablets taken from the following 2 fields Dispensed and Returned, this will allow the number to be entered into field Number taken

Placed the following in control source
=Int(Sum([Dispensed]-[Returned]))

This works but it won't do the calculations on the fly!!!! You need to go onto the next record then it gives figure.

The second calculation needs to work out a compliance figure, with the following fields:
Number taken, Number to be taken and this needs to be a percentage so it needs multiplying by 100.

Placed the following in control source
=Int(Sum([Number taken]\[Number to be taken])*100)

This just doesn't do anything.

Anybody any good with Access?? Help is as unhelpful as help normally is and I can't find any exampls on the web. Is it possible to have 2 calculations on a form?

I've got no ideas how to get this to work so any help is much appreciated.

thanks

Edited by stidyup, 16 August 2005 - 06:31 AM.

### #2 stidyup

stidyup
• Topic Starter

• Members
• 641 posts
• OFFLINE
•
• Gender:Male
• Local time:09:54 AM

Posted 17 August 2005 - 05:14 AM

Sorted it got some help else where but this was the solution.

Code needed was:

=Round(Sum(([Number taken]*100)/([Number to be taken]*100)),4)

Seems very bad maths to me to get it to work but it does.

This site lists functions available to Access which I eventually sussed how to use a tiny bit..........

Plus

Try adding (Event Procedure code) Me.Refresh

### #3 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,588 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:09:54 AM

Posted 27 August 2005 - 09:39 PM

1. Use INT() if you must. It's pointless if you track whole tablets. Especially on subtraction. What is the SUM() doing? Sum of one record? Several?
2. ROUND() is appropriate. Follow current rules about x.5 to round up or down.
3. Use standard calculations. Complications you've shown are a bad business practice and are unlikely to pass by your compliance (or FDA).
4. Behind every field on your form, under properties, you can code what to do when certain evens happen, such as data change, or about to save. Code in the data change event can refresh your calculation on the fly if you refer to the calculated field properly. Use dropdown objects (form, fields) and dropdown functions to manipulate them.
5. Consider adding a third field to your calculation, in order to track
#dispensed
#returned, same claim, same product
#returned, different claim and/or different product
People think they can get money back by returning all sorts of junk - aspirins, vitamins, tums ... as if the labs couldn't tell
Hope this helps you a tiny bit.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users