formulas in access 1

[compudude86]

hi all,
im trying to make a formula to update an item automatically. example

column a column b column c
123 456 789


my formula would be column c = column a - column b / column a
i tried Me((column c)) = Me!((column a)) - ((column b)) / Me!((column a)) without the parentheses.
how would i actually get this to work properly in access?

 

[TomCologne]

Spaces in field names cause problems in Access unless the name is in brackets, eg [column a].

Putting brackets around the names might fix your problem, avoiding spaces will prevent those problems in the future.

TomCologne

 

[fneily]

First, please post Access questions in the Access forums where you will get more responses.

I'm assuming you're talking about a textbox on a form and not a table. Also, the textbox for the answer is an UNBOUND textbox. You DO NOT save calculations in a table.
On the AfterUpdate event of textboxes for [Lolumn A] and [Column B], you can put:

Me![Column C] = Me![column a] - Me![column b]/Me![Column b]

Or, on the Control source of the textbox for column c, you can put
= Me![column a] - Me![column b]/Me![Column b]

and then put Me![Column B].Requery on the AfterUpdate events.

 

[compudude86]

ok, so lets say, i use the expression builder and set column c's control to =[column a]-[column b]/[column a], when i do that, it does not equal a-b/a, if i put it under afterupdate it doesnt work like i expect it to, like when i change column a or b it sets column c to the nearest number to column b.

 

[MTBChik]

If you need to calculate on your table, have you thought about an append query?

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[fneily]

To help us out here, what are your EXACT field names?
And do you want Field1-Field2/Field1 OR (Field1-Field2)/Field1? Remember the Order of Operations?

 

[compudude86]

wouldnt that add more rows?

it goes like this:

column a column b column c
1155 3333 .65

any time i change column a or b, i want it to show in column c, so

column a column b column c
1125 3333 .66

but right now its calculating improperly

 

[compudude86]

ok,

its finding a gross profit margin, GPM, which is sold price - net price divided by the sold price again

srp netbtl gpm
12.00 5.00 0.58

but when i do it its telling me my gpm is .11

 

[compudude86]

jeez, it was order of operations, i was using the expression my boss gave me, he didnt mark it in parenteses, thats what messed me up, so i did it as column c = (column b - column a)/ column b and it worked!!! thank you very much fneily!!!!!

 

[fneily]

Maybe your boss should work for you.

 

[compudude86]

yea lol, thanks again, i know ill be back, next step of the project is to take an excel sheet and merge prices from the excel sheet into the databases existing prices, that one looks a little difficult from what ive read elsewhere

 

[fneily]

For ease, can you place the new/updated prices with their corresponding product name on a separate excel sheet? Then, all you would have to do, is to write an Access macro which uses the TransferSpreadsheet action to place the data in a temp table. Then you can have the same macro run Update and/or Append queries to change/add the prices to the Access table holding the data.

 

[compudude86]

ok, heres one, anyone know how to make it output a percentage, i.e no decimal point or zero, on my output?

 

[fneily]

Have your division done in an UNBOUND textbox. Then for Format select Percent and for Decimal Places select 0.

 

[compudude86]

ok, i figured it out, but now how do i get it to show up in my reports? those still show blanks

 

[fneily]

I don't understand. Do you have an UNBOUND texbox on your report with an expression as Control Source? What is your calculation - .11 or 11% or 11? You must be more specific. And what did you figure out?

 

[compudude86]

sry, i didnt realize that i needed to do the same thing in the report design as i do in the form design, so it all works now, thanks!!