quantity*price returns an error

choubix

Board Regular
Joined
Jul 3, 2008
Messages
80
hello,

I am using MS Query to join 3 worksheets together.
I need to calculate one field but it keeps returning an error.
the 2 fields are part of the same table calle "Trades"

The fields are : Quantity and USD Price

USD Price is calculated initially in the worksheet using the current exchange rate.
I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error.

Things i have tried:
I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error.

I also tried to copy the values (not the formula) into a new column called USD Price2 and:
- Quantity*USD Price2 : syntax error
- USD Price2*2: Syntax error

does anyone have an idea on how to deal with this please?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
is USD Price a number or text ?

Have you tried forcing the value to number prior to multiplying ?

Also have try renaming USD Price2 to USDPrice2 (no spaces)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
What format is the USD Price column in? It sounds like it my be being interpreted as text rather than numeric values. Does the data in the column look like:

USD 30.45

for example? This would be a text value instead of a numeric.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

I think Luke's correct - it could be the way your column is written. Try writing the expression as:

Quantity*[USD Price2]

Note the square brackets around "USD Price2"
 

choubix

Board Regular
Joined
Jul 3, 2008
Messages
80
you guys rock :)
thanks!

so the [] are used if the column is considered "text" and not numbers??
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

nope, the [ ] handles spaces in field names, like 'sheet 1' is used in Excel for sheets with spaces -- sheet 1!A1 would fail whereas 'sheet 1'!A1 would not.

as a general rule I try to avoid having spaces in db fields.
 

choubix

Board Regular
Joined
Jul 3, 2008
Messages
80
it seems to be good practice indeed to avoid spaces.
will change my tables then

jsut one more thing:

it seems that the consolidated data is fine (the data pull out using the MS Query) BUT my pivot table is incorrect.

I am displaying the Qtt * Price USd calculated earlier (center of the pivot table) by account AND dates (top of the pivot table) by Stock (left side of the pivot table)

when I collapse the "dates" it shows the Qtty * Price in USD for the accounts by stock. the thing is: the grand total is then incorrect :(

anybody has ever encountered such a problem???
 

choubix

Board Regular
Joined
Jul 3, 2008
Messages
80
forgot what I have said!!
works perfectly well!!!

thanks guys, really helped me out on this one! :)
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Just for info

Regardings the syntax, I always use `for names with spaces` and use [for new field names]. And 'AroundText'. As below. 99.9% of my querying is from Excel files.

Code:
SELECT `field name with space`, `and another` * `what ever` AS [New Field Name]
FROM `long file path`.`table name containing space` AS Alias
WHERE `Currency` = 'USD' AND ID_No = 123
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

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