plus and negative on same field

[jwrnana]

I have a rather large query that has a column (Total Invoice Line Items)
that include some positives and negatives. I would like to have the same
field, but one field lists all of the positives and one lists the negatives
in a separate column.

Novice

 

[jwrnana]

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total Price,
NULL) and got invalid syntax. I took the space out between total and price,
but did not work. My field name is Total Price. I placed this where you
build an expression. I tried to place in criteria as >0 and <0 and got no
information.

 

[jwrnana]

John -- thank you, that worked! One problem remains -- I have fields that
give me order date, invoice number, customer info, order info and order
details, and payment information. When the payment information displays, it
displays the total amount paid for EACH line item, rather than one amount
for each invoice. Can this be changed? Also, I would like line item
information as well as one invoice total. Thank you.

Pardon me, but if you have spaces in your field name (or other non-Alpha
characters) you should surround the field name with []. So try,

Positive: IIF([Total Price]>=0, [Total Price],NULL)

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total Price,
NULL) and got invalid syntax. I took the space out between total and price,
but did not work. My field name is Total Price. I placed this where you
build an expression. I tried to place in criteria as >0 and <0 and got no
information.

 

[jwrnana]

Can you advise?

John -- thank you, that worked! One problem remains -- I have fields that
give me order date, invoice number, customer info, order info and order
details, and payment information. When the payment information displays, it
displays the total amount paid for EACH line item, rather than one amount
for each invoice. Can this be changed? Also, I would like line item
information as well as one invoice total. Thank you.

Pardon me, but if you have spaces in your field name (or other non-Alpha
characters) you should surround the field name with []. So try,

Positive: IIF([Total Price]>=0, [Total Price],NULL)

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total Price,
NULL) and got invalid syntax. I took the space out between total and price,
but did not work. My field name is Total Price. I placed this where you
build an expression. I tried to place in criteria as >0 and <0 and

got

no

 

[John Spencer (MVP)]

You want details and summary information in the same query-not impossible but a
bit complex. Are you planning to use this query to print a report? If so, use
the report to get your totals, by using the grouping dialog.

If you are planning to use your query as the source of a form or just looking at
the query directly then you have a problem. You need to post the SQL of the
query and perhaps Tom Ellison or I can suggest how you would do what you want to
do.

From my limited understanding you would need to either use a subquery or a
Domain Aggregate function (DSUM) to get what you are asking for and it would in
all probability be slow. Also, the total would have to be in each row.

Alternatively you might look at three queries. The one you have now, a query to
get the Total, and a UNION query to combine query one and query two. (not
updatable)

Can you advise?

John -- thank you, that worked! One problem remains -- I have fields that
give me order date, invoice number, customer info, order info and order
details, and payment information. When the payment information displays, it
displays the total amount paid for EACH line item, rather than one amount
for each invoice. Can this be changed? Also, I would like line item
information as well as one invoice total. Thank you.

Pardon me, but if you have spaces in your field name (or other non-Alpha
characters) you should surround the field name with []. So try,

Positive: IIF([Total Price]>=0, [Total Price],NULL)

jwrnana wrote:

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total Price,
NULL) and got invalid syntax. I took the space out between total and price,
but did not work. My field name is Total Price. I placed this where you
build an expression. I tried to place in criteria as >0 and <0 and

got

no
information.
I suggest you use two calculated columns in the query, perhaps like this:

Positives: IIF(YourColumn >= 0, YourColumn, NULL)
Negatives: IIF(YourColumn < 0, YourColumn, NULL)

You did not say in which column zero values would be placed. Perhaps
neither. However, I have placed them in the Positive column.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
I have a rather large query that has a column (Total Invoice Line Items)
that include some positives and negatives. I would like to have the
same
field, but one field lists all of the positives and one lists the
negatives
in a separate column.

Novice

 

[jwrnana]

I still need help with this problem. Thank you

Can you advise?

John -- thank you, that worked! One problem remains -- I have fields that
give me order date, invoice number, customer info, order info and order
details, and payment information. When the payment information

displays,

it
displays the total amount paid for EACH line item, rather than one amount
for each invoice. Can this be changed? Also, I would like line item
information as well as one invoice total. Thank you.

Pardon me, but if you have spaces in your field name (or other non-Alpha
characters) you should surround the field name with []. So try,

Positive: IIF([Total Price]>=0, [Total Price],NULL)

jwrnana wrote:

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total Price,
NULL) and got invalid syntax. I took the space out between total

and
price,

but did not work. My field name is Total Price. I placed this

where
you

build an expression. I tried to place in criteria as >0 and <0 and

got

no
information.
I suggest you use two calculated columns in the query, perhaps

like
this:

Positives: IIF(YourColumn >= 0, YourColumn, NULL)
Negatives: IIF(YourColumn < 0, YourColumn, NULL)

You did not say in which column zero values would be placed. Perhaps
neither. However, I have placed them in the Positive column.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
I have a rather large query that has a column (Total Invoice

Line
Items)

that include some positives and negatives. I would like to have the
same
field, but one field lists all of the positives and one lists the
negatives
in a separate column.

Novice

 

[jwrnana]

Thank you. The positive/negative info solved one problem. The following is
an example of what I am needing as well.

Example -----

Invoice # = 1
Order Date =6/02/2003
Ship Date = 6/03/2003
Customer Info = John Doe, Address
Order Info = Qty of 1; Tractor; Unit Price = $10,000; total Price = $10,000
Qty of 4; Mowers,Unit Price = $400; total Price = $1600
Qty of 2; Blowers, Unit Price = $200; total Price =
$400
Invoice Total = $12,000
Payment Date = 6/05/2003
Payment Amount = $12,000


This is all on one query. However, when the payment amount lists, it puts
$12,000 by each line item suggesting that on Invoice #1, we received $12,000
X 3 (line items) for a total of $36,000.
I do not get the invoice total either; only total price of quantity X unit
price for each line item ( I do need this information as well).

This information needs to be on one query since I need to transmit via
Microsoft Excel to another office. When I try to send as a form it does not
put it in proper format.

Thank you, Joy

As I noted on Monday, I have no real idea what you want. Can you enter a sample
of what you expect the output to look like. Also, do you need to do this in a
report or in a query. If you need it in a query, does the work all have to be
accomplished in ONE query.

I still need help with this problem. Thank you

Can you advise?
John -- thank you, that worked! One problem remains -- I have

fields
that

give me order date, invoice number, customer info, order info and order
details, and payment information. When the payment information displays,
it
displays the total amount paid for EACH line item, rather than one amount
for each invoice. Can this be changed? Also, I would like line item
information as well as one invoice total. Thank you.


Pardon me, but if you have spaces in your field name (or other non-Alpha
characters) you should surround the field name with []. So try,

Positive: IIF([Total Price]>=0, [Total Price],NULL)

jwrnana wrote:

Tom -- I entered the following: Positive: IIF(Total Price>=0, Total
Price,
NULL) and got invalid syntax. I took the space out between

total
and

price,
but did not work. My field name is Total Price. I placed this where
you
build an expression. I tried to place in criteria as >0 and <0 and
got
no
information.
I suggest you use two calculated columns in the query, perhaps like
this:

Positives: IIF(YourColumn >= 0, YourColumn, NULL)
Negatives: IIF(YourColumn < 0, YourColumn, NULL)

You did not say in which column zero values would be placed.
Perhaps
neither. However, I have placed them in the Positive column.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
I have a rather large query that has a column (Total Invoice Line
Items)
that include some positives and negatives. I would like to have
the
same
field, but one field lists all of the positives and one

lists
the

negatives
in a separate column.

Novice