Excel Calculations in Access

[siamsunset]

Hey Everyone,

I am building a qoutes form and have to do alot of calculations to get our end result. Do i have to do queries for all of these calculations?? Is it possible to do the calculations in design form mode? All of my calcs will be preformed with in the same table.
Thanks in advance
Ben

 

[mdmilner]

Ok - I guess you can say I'd rather address my own errors than simply ignore them. I'm not saying I was completely wrong with my summation, but, I was wrong.

Yes, I see what you're talking about on using the forms & unbound controls. Can also see how they might be useful in some circumstances.

Mike

 

[SydneyGeek]

Ben, to convert this formula to Access
=IF(F13/E14<5001,F13/E14*1.1,F13/E14*1.05)

You'd need to do a couple of things.
1. Work out what fields E and F represent -- say, [Data1] and [Data2]
2. Copy and paste your formula to Notepad. Replace the following:
IF with IIF
E14 with Nz([Data1])
F13 with Nz([Data2])

Now, copy that formula into your control or query.

BUT...
I noticed that you had F13/E14. Is that meant to be different RECORDS, or a value divided by a total? If it's either, you won't be able to do it in a single record on a form because Access doesn't "do" comparisons to the previous or next record easily.
However, if you're dividing a single value by the TOTAL, you can do that using a form / subform approach. Post back if you need more info.

Denis

 

[siamsunset]

Basically its a division.

Divide [data1] by [data2] and if it is less than 5000 mulitply by 1.1 but if not mulitply by 1.05

Also , I want to multiply a figure in another table by a figure in a text box. For example On my qoutes form i want to mulitply then figure i enter in my txtkimo text box by a figure located in the tblpress/kimolec feild and return that figure into txtkimototal which then enters the result into the totalkimo field in the qoutes table. Do i need a query for this or is it simular to excel where i just have to have it look in the correct field in the formula?

 

[SydneyGeek]

I think you're best off pulling the value from the other table in via a query. You can then include that in any calculations you need.
Use the approach I mentioned before to get your expression for the textbox. Basically, you use IIF instead of IF. Also, you use field names instead of cell addresses, otherwise the logic is the same.

Denis

 

[Tanis]

Just substitute control names for cell references.

Private Sub E14_AfterUpdate()
If ([F13] / [E14] < 5001) Then
Me.Result = Me.F13 / Me.E14 * 1.1
Else
Me.Result = Me.F13 / Me.E14 * 1.05
End If
End Sub

So, after an entry is made in text box name E14 it triggers the code and puts the result in text box Result.