Math 101

TKT_BEER

New Member
Joined
Dec 22, 2003
Messages
38
Hi need your help on the following...

In a form I have these fields...
Item_cost
Item_qty
Extende_price

What do I need to do so that the extended_price field multiplies the item_cost times the item_qty automatically to get the total cost?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Dr_Worm

Board Regular
Joined
Jul 28, 2003
Messages
103
If all you want to do is display the total cost on the form create a text box and then in Properties type in the Control Source- "=[Item_cost]*[Item_qty]"
 

TKT_BEER

New Member
Joined
Dec 22, 2003
Messages
38
Got that! OK what if I want that result to be added to the table this form is working of?

thx for the response!!
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
First, you shouldn't put a calculated value into a table.
What you should do is remove Extende_Price and calculate it in a query, then use that query wherever you need it.

To do it your way - to update any field, use something like the below for your query.

UPDATE tblName SET Extende_Price=Item_Cost*Item_Quantity;

To do it my way.

SELECT Item_Cost, Item_Quantity, [Item_Cost]*[Item_Quantity] As Extended_Price FROM tblName;

To drop in both - here's the cliff-notes.
Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.

You can change it back to design view (right click blue-bar at top) and select Query View to see what it looks like in QBE (Query by Example)


Mike
 

jobb

Board Regular
Joined
Apr 24, 2003
Messages
78

ADVERTISEMENT

what I would do is have a macro to run AfterUpdate for both the cost and quantity fields (AfterUpdate is under the properties for the text boxes)

the action would be to SetValue of your [Extende_price] field bound on the form to [cost]*[quantity].
 

TKT_BEER

New Member
Joined
Dec 22, 2003
Messages
38
where should I Drop the query to do it my way?

and where should I drop the query doing it your way?

I want to test out both
thx!
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
MS Access gives you something called QBE (Query By Example).
It's a "Wizard" interface making it easier for new uers to build SQL statements. By default, when you attempt to create a new query, it starts you in QBE but you can choose to do your query as SQL directly.

My above instructions were to do exactly the latter, paste SQL directly into it to run. Quoting myself:

Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.

There are other ways (keystrokes and keyclicks) to do the same thing but the SQL syntax doesn't change.

My suggestion is, figure out how to do it with a basic query first. Then worry about attempting to add buttons to forms as Dr_Worm and jobb are suggesting.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top