quantity*price returns an error

[choubix]

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?

 

[choubix]

nobody's got an idea??

 

[DonkeyOte]

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]

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]

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]

you guys rock
thanks!

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

 

[DonkeyOte]

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]

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]

forgot what I have said!!
works perfectly well!!!

thanks guys, really helped me out on this one!

 

[Fazza]

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.

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