Query +/_ Currency Problem

[Nigel Bate]

Hi,
I am linking to a sales table from a 3rd party database. The sales
tables holds the values of sales as well as any credits made to a sale but
are in the same field SumCurrency. From this table I am attempting to
calculate using 2 fields 'SumCurrency' and OrderType'. The difference being
the type of order. For a sale - order type is 1, for a credit - order type
is 2. I am bring these fields into a report that shows the balance of what
is owed a balance, for a client and therefore there may be several orders
made against and some credits in the report.
My problem is that for an order (Type1) I need the positive value and for a
credit (type 2) I need this to be a negative value. I.e. Type 1 - £1000,
Type 2 = -£1000.
I have try'd to do this on a report (OnPage), but depending on the number of
rows the total sum if different.
Please anyone help with regards to a query solution ?
Regards
Nigel Bate

 

[Allen Browne]

In query design view, type something like this into a fresh column in the
Field row:
Amount: IIf([OrderType]=1, [SumCurrency], -[SumCurrency])

 

[Nigel Bate]

Allen,
Many thanks, unfortunetly I get '#Error' as a result when i run the
query !
Regards
Nigel

In query design view, type something like this into a fresh column in the
Field row:
Amount: IIf([OrderType]=1, [SumCurrency], -[SumCurrency])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am linking to a sales table from a 3rd party database. The sales
tables holds the values of sales as well as any credits made to a sale
but are in the same field SumCurrency. From this table I am attempting to
calculate using 2 fields 'SumCurrency' and OrderType'. The difference
being the type of order. For a sale - order type is 1, for a credit -
order type is 2. I am bring these fields into a report that shows the
balance of what is owed a balance, for a client and therefore there may
be several orders made against and some credits in the report.
My problem is that for an order (Type1) I need the positive value and for
a credit (type 2) I need this to be a negative value. I.e. Type 1 -
#1000, Type 2 = -#1000.
I have try'd to do this on a report (OnPage), but depending on the number
of rows the total sum if different.
Please anyone help with regards to a query solution ?
Regards
Nigel Bate

 

[Nigel Bate]

Allen, or I get the following message 'This expression is typed incorrectly,
or it is too complex to be evaluated'.

Nigel

Allen,
Many thanks, unfortunetly I get '#Error' as a result when i run the
query !
Regards
Nigel

In query design view, type something like this into a fresh column in the
Field row:
Amount: IIf([OrderType]=1, [SumCurrency], -[SumCurrency])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am linking to a sales table from a 3rd party database. The sales
tables holds the values of sales as well as any credits made to a sale
but are in the same field SumCurrency. From this table I am attempting
to calculate using 2 fields 'SumCurrency' and OrderType'. The difference
being the type of order. For a sale - order type is 1, for a credit -
order type is 2. I am bring these fields into a report that shows the
balance of what is owed a balance, for a client and therefore there may
be several orders made against and some credits in the report.
My problem is that for an order (Type1) I need the positive value and
for a credit (type 2) I need this to be a negative value. I.e. Type 1 -
#1000, Type 2 = -#1000.
I have try'd to do this on a report (OnPage), but depending on the
number of rows the total sum if different.
Please anyone help with regards to a query solution ?
Regards
Nigel Bate

 

[Allen Browne]

Tell us about these fields

Is OrderType a field in your table? If so, open the table in design view,
and see what the data type of the field is. If Text (not Number), you need
extra quotes:
Amount: IIf([OrderType]="1", ...

Now tell us about the SumCurrency field. Is it a table field? Of type Number
or Currency? If Text it will require massaging to sum, using Nz() and Val().
If it is a calculated query field, Access may not understand it correctly.
More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Of course, it could also be something simpler, such as a misspelled name, or
a naming clash, It could even be a bad reference:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Many thanks, unfortunetly I get '#Error' as a result when i run the
query !
Regards
Nigel

In query design view, type something like this into a fresh column in the
Field row:
Amount: IIf([OrderType]=1, [SumCurrency], -[SumCurrency])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am linking to a sales table from a 3rd party database. The sales
tables holds the values of sales as well as any credits made to a sale
but are in the same field SumCurrency. From this table I am attempting
to calculate using 2 fields 'SumCurrency' and OrderType'. The difference
being the type of order. For a sale - order type is 1, for a credit -
order type is 2. I am bring these fields into a report that shows the
balance of what is owed a balance, for a client and therefore there may
be several orders made against and some credits in the report.
My problem is that for an order (Type1) I need the positive value and
for a credit (type 2) I need this to be a negative value. I.e. Type 1 -
#1000, Type 2 = -#1000.
I have try'd to do this on a report (OnPage), but depending on the
number of rows the total sum if different.
Please anyone help with regards to a query solution ?
Regards
Nigel Bate

 

[Nigel Bate]

Allen,
Brilliant, The code works absolutly great with the ' '. The OrderType is a
text field, and the Currency field is Number, Decimal.
Many Many thanks for your help & patience.
Regards
Nigel

Tell us about these fields

Is OrderType a field in your table? If so, open the table in design view,
and see what the data type of the field is. If Text (not Number), you need
extra quotes:
Amount: IIf([OrderType]="1", ...

Now tell us about the SumCurrency field. Is it a table field? Of type
Number or Currency? If Text it will require massaging to sum, using Nz()
and Val(). If it is a calculated query field, Access may not understand it
correctly. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Of course, it could also be something simpler, such as a misspelled name,
or a naming clash, It could even be a bad reference:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Many thanks, unfortunetly I get '#Error' as a result when i run the
query !
Regards
Nigel

In query design view, type something like this into a fresh column in
the Field row:
Amount: IIf([OrderType]=1, [SumCurrency], -[SumCurrency])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I am linking to a sales table from a 3rd party database. The sales
tables holds the values of sales as well as any credits made to a sale
but are in the same field SumCurrency. From this table I am attempting
to calculate using 2 fields 'SumCurrency' and OrderType'. The
difference being the type of order. For a sale - order type is 1, for a
credit - order type is 2. I am bring these fields into a report that
shows the balance of what is owed a balance, for a client and therefore
there may be several orders made against and some credits in the
report.
My problem is that for an order (Type1) I need the positive value and
for a credit (type 2) I need this to be a negative value. I.e. Type
1 - #1000, Type 2 = -#1000.
I have try'd to do this on a report (OnPage), but depending on the
number of rows the total sum if different.
Please anyone help with regards to a query solution ?
Regards
Nigel Bate