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

 

[Andrew Fergus]

Hi Ben

Yes you can do calculations on the form (or the report) but my personal preference is to do complex calculations in a query.

(.... dare I ask ....) What exactly are you trying to do?

Andrew

 

[mdmilner]

What Andrew means is this. There really is only one way to calculate values based on fields within a table -- the main difference is how 'visual' the tools are to figure out how to do things.

For example, you can build a query and then base a form off that query...or, you can manually type the SQL statement for that query into a Form's recordsource. Both actions have an identical result, but, it's far far easier to use the QBE Wizard (query by example -- "Design Mode" for queries) than it is to just do the SQL if you haven't done it before.

Mike

 

[Norie]

Mike

How about using unbound controls and update/exit events of bound controls to do calculations on a form?

 

[mdmilner]

If you want to use unbound controls, well, go for it and good luck.
[dramatic_moment]
Sure it'll work, but only at the price of your programming soul.
[/dramatic_moment]

Mike

 

[siamsunset]

Thanks for he reply,

I understand the use of calculations in queries but how do i go about using formula's in unbound controls and events?

Ben

 

[Andrew Fergus]

Hi Ben
This depends on what you want to do. This recent thread has a relatively simple example of one way of doing it, as Norie was alluding to in his earlier post.
HTH, Andrew.

 

[siamsunset]

Thanks heaps for you help.

Just 2 questions, i can do these calculations using multiple tables? for example, a price from table A + a price from table B and have the result record in table C?

Also i followed the directions in the link provided and it works ok but to resolve the equation i have to enter a number in the total box and hit enter for it to display the answer to my equation. Is there a way that is i enter in the value in txtbox1 and txtbox2 and the answer autofills in txtbox3?

 

[SydneyGeek]

Ben, if you want to push values from a combo to textboxes, you can follow the suggestions in your earlier thread. To push calculations as well, you could do something like:
[txtCalcPrice] = nz([txtPages])*(nz([txtPaper])+nz([txtInk]))
This needs to be in the AfterUpdate events of the Paper combo, the Ink combo, and the two textboxes [txtPaper] and [txtInk]. For the combos, it should come AFTER the code which pushes values to the texboxes, ie the last line before End Sub in the routine.
That way, if you update either of the combos or manually change the textboxes, the calc will refresh. [txtPaper], [txtInk] and [txtCalcPrice] should all be linked to fields in the Quotes table.


The nz function converts null values to zero so Access doesn't choke on blank fields.

Denis

 

[siamsunset]

Ok i see where i went wrong now,


Last question and i will crawl back into the hole i came from, how would i convert this formula?

=IF(F13/E14<5001,F13/E14*1.1,F13/E14*1.05)

Thanks again
Ben