Multiple calculations in a single query

[gilescis]

I have 2 calculations in a single query.

1st - ProdCost: [TBLinvqty]*67

2nd - Gross: [TBLinvamt]-[ProdCost]-[TBLfrtamt]-[TBLocamt]

The query runs and works fine but it keeps prompting me for ProdCost but if I hit ok it runs fine.

How do I get it to quit prompting me for Prodcost

BTW, Prodcost is not a field in a table its only the 1st calculation in this query

 

[chergh]

try:

[TBLinvqty]*67 AS Prodcost

 

[chergh]

Actualy just do:

[TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]

 

[gilescis]

That worked great, So I just need to rememeber to redue the same calc in the second calculation

Thanks

 

[boblarson]

Actualy just do:

[TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]

Make sure to use the correct parentheses too or your mathematical formula will not work correctly:

[TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]

is NOT the same as:

[TBLinvamt]-([TBLinvqty]*67)-[TBLfrtamt]-[TBLocamt]

 

[The_Doc_Man]

The prompt for a value is because Access cannot find the entity you named in some query element. I.e. the suggestion to supply the "AS fieldname" clause for the computation - or the suggestion to use only the correct names of the underlying fields.

The "parameter" prompt is Access's quirky little way of saying "I don't know what this is."

The most common two causes for this are spelling errors and failure to properly qualify the source when referencing a field that could come from any of at least two places.

 

[chergh]

Make sure to use the correct parentheses too or your mathematical formula will not work correctly:

[TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]

is NOT the same as:

[TBLinvamt]-([TBLinvqty]*67)-[TBLfrtamt]-[TBLocamt]

Err yes it is, last time I checked multiplication and division was performed before addition and subtraction.

 

[boblarson]

Err yes it is, last time I checked multiplication and division was performed before addition and subtraction.

Okay, I'll give you that one, but it would be an easier read for someone coming in to see what you're trying to accomplish. And if you get in the habit of putting it in explicitly, it won't accidentally come back to bite you if you really mean something else.

 

[chergh]

It's certainly a good point and something I do in my own code, I probably would have included it here except I was just about to leave work.