Need to caculate in Access

[Stephanie53]

I need to caculate qty with price in a form. Can someone help me with the vba on this?

[June7]

Why VBA?

Calculate in query or in textbox.

[qty] * [price]

[Stephanie53]

thank you for the formula...ok...and?? where do i put in that formula ...lets say...query?

[June7]

In query, on the Field row, something like:

TotPrice: [qty] * [price]

In textbox ControlSource:

= [qty] * [price]

[Stephanie53]

ok I am in queary...I am in datasheet view..the columns I have are as follows:

Qty UnitCost ExtCost
0 42.63 $-00

now please tell me where I would put in this formula and will this formula contine on down? or have to be put in full column?

[Stephanie53]

I tried putting it the row and i get errors that is why i am asking more details...please treat me as if i never been in access before. I alwys have used VBA with the *IF* in it..but wasnt working

[June7]

Do this in the query design grid in Design View.

ExtCost: [Qty] * [UnitCost]

This will not save result to a field called ExtCost. That will require VBA code. However, should not save this calculated result to table.

[Stephanie53]

ok its still now working..and this is getting frustrating...ughhh

[June7]

Why isn't it working - error message, wrong results, nothing?

This is basic query design. Access Help has guidelines.

Review http://office.microsoft.com/en-us/ac...001181384.aspx
Go down to the section 'In the field cell in the design grid of a query'.

Or do the calc in textbox ControlSource.

[Stephanie53]

Ok lets just start over here.. I am in Access 2010 just so we are on the same page...

I am in Query Design View....I am under properties...no ControlSource..I am in Query..No ControlSource..All I have listed for each Field (which shows on the left) the following:

Field:
Table:
Sort:
Show:
Criteria:
or:

Under Field only gives the me the option to select which name field from the table.
Under Table only shows the table where query s getting the info from
and you should know the rest....

So please POINT ME in the correct direction as I Just Dont See It...

TIA!!

Stephanie

[June7]

In the query Field row you can type whatever you want into a cell.


ControlSource is property of textbox control on form or report.


I have pointed you in the right direction. I have even referred you to tutorial with explicit illustrated instructions. Access Help has guidelines.

You have two options, both are very basic Access functionality.

1. calculate in query

2. calculate in textbox ControlSource property

In both the fields you want to calculate must be in the same dataset (table or query that joins tables). I don't know your data structure other than what you stated that both fields are available when the query is in design view.

[Stephanie53]

alright thanks!

[June7]

Sorry, because you are using Access 2010 there is a third basic option.

Access 2010 has a new field type - Calculated. This allow some calculations (such as what you want, i.e. field1 * field2) to be set up in a table. Again, Access Help has guidelines on this field type.

Use of this field type makes the file incompatible with Access 2007. Most long-time experienced developers avoid this new field type and do calculations in query or textbox.

[Stephanie53]

I have tried that option too and got the following:

Run-time error '2147352567(80020009)
you cant assign a value to this object

I will have to find some way to get this to work..thanks again for trying but it seems nothing is working to get this to caculate..I am about to toss this computer out the window!

Stephanie

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.