Query result won't display negative currency.

[Rzig]

I import an Excel file into a table, which properly formats negative values
with a minus sign in front of the number. However, when I run the query,
based only on the table, the negative number is transposed to a positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be currency.
Any ideas?

 

[Carl Rapson]

I import an Excel file into a table, which properly formats negative values
with a minus sign in front of the number. However, when I run the query,
based only on the table, the negative number is transposed to a positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

 

[Rzig]

Pardon my density(I've not posted a SQL before), but is this what you wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));

I import an Excel file into a table, which properly formats negative values
with a minus sign in front of the number. However, when I run the query,
based only on the table, the negative number is transposed to a positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

 

[Carl Rapson]

Pardon my density(I've not posted a SQL before), but is this what you
wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));

I import an Excel file into a table, which properly formats negative
values
with a minus sign in front of the number. However, when I run the
query,
based only on the table, the negative number is transposed to a
positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

The Amount field is the field you're having a problem with? There's
certainly nothing in the query that would be changing negative values to
positive, so the next thing to look at is the data in the table itself. When
you open the table directly, are the values negative? If so, try re-creating
your query from scratch and see if the problem still occurs.

Carl Rapson

 

[Rzig]

Ok, here's a puzzle.
When I recreate the query using design view, the problem persists.
However, when I used the Simple Query Wizard, the negative values show as
negative and my report properly calculates the math.
Unfortunately, I need the query to filter two fields([payee] & [Transaction
type]), so that some records don't show in the report.
When I edit the query and add the needed criteria to only those fields and
don't touch the [amount] field, the properly working query suddenly renders
the negative number as a positive again.
Makes no sense to me.

Pardon my density(I've not posted a SQL before), but is this what you
wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));

I import an Excel file into a table, which properly formats negative
values
with a minus sign in front of the number. However, when I run the
query,
based only on the table, the negative number is transposed to a
positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

The Amount field is the field you're having a problem with? There's
certainly nothing in the query that would be changing negative values to
positive, so the next thing to look at is the data in the table itself. When
you open the table directly, are the values negative? If so, try re-creating
your query from scratch and see if the problem still occurs.

Carl Rapson

 

[Rzig]

I think I found my problem. Thank you, so much, for your help.

Pardon my density(I've not posted a SQL before), but is this what you
wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));

I import an Excel file into a table, which properly formats negative
values
with a minus sign in front of the number. However, when I run the
query,
based only on the table, the negative number is transposed to a
positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

The Amount field is the field you're having a problem with? There's
certainly nothing in the query that would be changing negative values to
positive, so the next thing to look at is the data in the table itself. When
you open the table directly, are the values negative? If so, try re-creating
your query from scratch and see if the problem still occurs.

Carl Rapson

 

[Richard]

I think I found my problem. Thank you, so much, for your help.

Pardon my density(I've not posted a SQL before), but is this what you
wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));
:
I import an Excel file into a table, which properly formats negative
values
with a minus sign in front of the number. However, when I run the
query,
based only on the table, the negative number is transposed to a
positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?
Can you post the SQL of your query?
Carl Rapson

The Amount field is the field you're having a problem with? There's
certainly nothing in the query that would be changing negative values to
positive, so the next thing to look at is the data in the table itself. When
you open the table directly, are the values negative? If so, try re-creating
your query from scratch and see if the problem still occurs.

Carl Rapson- Hide quoted text -

- Show quoted text -

You say you found your problem, but do not say HOW you solved it. If
you would, please let the rest of (who have been following) us know
how you solved it, so that we too can learn.

Richard

 

[Rzig]

I don't know if my solution will be helpful. It turned out my problem was
one of poor filtering. The table I was querying contained both a positive
and negative amount for some transactions, to create a zero balance for
accounting purposes.
My error resulted from filtering only the negative transactions, leaving the
positive transaction in the results, thus skewing my sums.
I didn't realize my error, until I followed Carl's advice and repeated the
process or creating the query a second time.

I think I found my problem. Thank you, so much, for your help.

Pardon my density(I've not posted a SQL before), but is this what you
wanted?
SELECT TblLedger.Amount, TblLedger.Claim, TblLedger.Claimant,
TblLedger.Line, TblLedger.Payee, TblLedger.ServiceType, TblLedger.City,
TblLedger.State, TblLedger.CheckNumber, TblLedger.IssueDate,
TblLedger.StartDate, TblLedger.EndDate, TblLedger.Total
FROM TblLedger
WHERE (((TblLedger.Payee)<>"allocation to file") AND
((TblLedger.TransactionType)="Loss Payment"));

"Carl Rapson" wrote:

I import an Excel file into a table, which properly formats negative
values
with a minus sign in front of the number. However, when I run the
query,
based only on the table, the negative number is transposed to a
positive
number. Thus, my report gives the wrong sum of the field.
Both the table and query are formatted for this field [amount] to be
currency.
Any ideas?

Can you post the SQL of your query?

Carl Rapson

The Amount field is the field you're having a problem with? There's
certainly nothing in the query that would be changing negative values to
positive, so the next thing to look at is the data in the table itself. When
you open the table directly, are the values negative? If so, try re-creating
your query from scratch and see if the problem still occurs.

Carl Rapson- Hide quoted text -

- Show quoted text -

You say you found your problem, but do not say HOW you solved it. If
you would, please let the rest of (who have been following) us know
how you solved it, so that we too can learn.

Richard