Query won't pull amounts less than zero

[Julia Baresch]

Hi everyone, I haven't found any reference to this problem on this
group or in Access 97 help. I hope someone here knows this - probably
a simple thing, but not obvious.

I designed a query to pull payment data from my database for
accounting. It has 3 tables with one-to-many joins:

Table1 --> one-to-many --> Table2 --> one-to-many --> Table3

The payment amount field is in Table3. I entered a few negative
numbers in this field for payment checks that have been returned and
voided. The field is currency data type, and it took the negative
entries and displays them with () like this ($500.00).

When I run the query with the payment field criteria set to <>0, it
won't pull the records with negative amounts. I tried it with <0 and
with no criteria, and it still won't pull these records.

I also tried making a copy of the table and the query for
experimentation and changing the data type to Long Integer with
currency format. It still won't pull the records with negative
amounts.

Can anyone tell me why these records won't pull and how to fix it?

Thank you very much!

Julia

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Can't really tell what's wrong without your SQL query.

Have you queried Table3 by itself - like this:

SELECT Payment
FROM Table3
WHERE Payment <= 0

If that works then its the criteria/joins to the other tables that are
excluding the negative payments. We'd have to know the SQL query and
some more info on how your tables are designed before we could give
better answers.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5WWcoechKqOuFEgEQIMnQCguYtKfYLDW2oL17npkApu1X UGU88An2ml
YB3MIf8titgL65ivyO79v5N8
=/1lB
-----END PGP SIGNATURE-----

Julia Baresch wrote:

Hi everyone, I haven't found any reference to this problem on this
group or in Access 97 help. I hope someone here knows this - probably
a simple thing, but not obvious.

I designed a query to pull payment data from my database for
accounting. It has 3 tables with one-to-many joins:

Table1 --> one-to-many --> Table2 --> one-to-many --> Table3

The payment amount field is in Table3. I entered a few negative
numbers in this field for payment checks that have been returned and
voided. The field is currency data type, and it took the negative
entries and displays them with () like this ($500.00).

When I run the query with the payment field criteria set to <>0, it
won't pull the records with negative amounts. I tried it with <0 and
with no criteria, and it still won't pull these records.

I also tried making a copy of the table and the query for
experimentation and changing the data type to Long Integer with
currency format. It still won't pull the records with negative
amounts.

Can anyone tell me why these records won't pull and how to fix it?

Thank you very much!

Julia

[Doug M]

"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50**************************@posting.google.c om...

Hi everyone, I haven't found any reference to this problem on this
group or in Access 97 help. I hope someone here knows this - probably
a simple thing, but not obvious.

I designed a query to pull payment data from my database for
accounting. It has 3 tables with one-to-many joins:

Table1 --> one-to-many --> Table2 --> one-to-many --> Table3

The payment amount field is in Table3. I entered a few negative
numbers in this field for payment checks that have been returned and
voided. The field is currency data type, and it took the negative
entries and displays them with () like this ($500.00).

When I run the query with the payment field criteria set to <>0, it
won't pull the records with negative amounts. I tried it with <0 and
with no criteria, and it still won't pull these records.

I also tried making a copy of the table and the query for
experimentation and changing the data type to Long Integer with
currency format. It still won't pull the records with negative
amounts.

Can anyone tell me why these records won't pull and how to fix it?

Thank you very much!

Julia

Is it possible that the negative amounts have been entered as ($500.00)
instead of -500?

Doug M

[Julia Baresch]

Thanks, I hadn't tried using just the one table. I will. I know the
amounts were entered as -500 because I entered them just before I did
this. We'll see what happens.

Thanks again :-)

"Doug M" <ow***@nowhere.com> wrote in message news:<3f***********************@news.optusnet.com. au>...

"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50**************************@posting.google.c om...

Hi everyone, I haven't found any reference to this problem on this
group or in Access 97 help. I hope someone here knows this - probably
a simple thing, but not obvious.

I designed a query to pull payment data from my database for
accounting. It has 3 tables with one-to-many joins:

Table1 --> one-to-many --> Table2 --> one-to-many --> Table3

The payment amount field is in Table3. I entered a few negative
numbers in this field for payment checks that have been returned and
voided. The field is currency data type, and it took the negative
entries and displays them with () like this ($500.00).

When I run the query with the payment field criteria set to <>0, it
won't pull the records with negative amounts. I tried it with <0 and
with no criteria, and it still won't pull these records.

I also tried making a copy of the table and the query for
experimentation and changing the data type to Long Integer with
currency format. It still won't pull the records with negative
amounts.

Can anyone tell me why these records won't pull and how to fix it?

Thank you very much!

Julia

Is it possible that the negative amounts have been entered as ($500.00)
instead of -500?

Doug M

[Julia Baresch]

I figured out what was causing this - it won't pull negatives in a
field set to currency format. I used my copy of table and form and
changed the format to Long Integer with Standard format, and then it
pulled negatives with no problem.

However, in the meantime I realized if we enter negatives in my
database it will throw off the totals, so we're not going to do that.

Thanks again for your help :-)

Julia

jb******@oldrepublic.com (Julia Baresch) wrote in message news:<50**************************@posting.google. com>...

Thanks, I hadn't tried using just the one table. I will. I know the
amounts were entered as -500 because I entered them just before I did
this. We'll see what happens.

Thanks again :-)

"Doug M" <ow***@nowhere.com> wrote in message news:<3f***********************@news.optusnet.com. au>...

"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50**************************@posting.google.c om...

Hi everyone, I haven't found any reference to this problem on this
group or in Access 97 help. I hope someone here knows this - probably
a simple thing, but not obvious.

I designed a query to pull payment data from my database for
accounting. It has 3 tables with one-to-many joins:

Table1 --> one-to-many --> Table2 --> one-to-many --> Table3

The payment amount field is in Table3. I entered a few negative
numbers in this field for payment checks that have been returned and
voided. The field is currency data type, and it took the negative
entries and displays them with () like this ($500.00).

When I run the query with the payment field criteria set to <>0, it
won't pull the records with negative amounts. I tried it with <0 and
with no criteria, and it still won't pull these records.

I also tried making a copy of the table and the query for
experimentation and changing the data type to Long Integer with
currency format. It still won't pull the records with negative
amounts.

Can anyone tell me why these records won't pull and how to fix it?

Thank you very much!

Julia

Is it possible that the negative amounts have been entered as ($500.00)
instead of -500?

Doug M

[Julia Baresch]

We decided to enter negatives in one special case, so I addressed this
again. I found when I changed the format of my payment field to
Standard from Currency, it wipes out or rounds everything to the right
of the decimal. Luckily I had remembered to make a backup copy of the
table! Further research shows the only way to have accurate decimal
numbers is to use the currency format. The other formats are either
no fractions, or use floating point calc.

I changed everything back to currency, and now the negative number
pulls in the query when it didn't before. The only difference is in
my query, field, and form the decimals are set to 2 places now instead
of auto. I'm not sure why it works now when it didn't before. Maybe
I used the wrong criteria when I tested it.

I posted this to help anyone else who runs into this problem, in case
you're wondering.

Cheers,
Julia :-)

jb******@oldrepublic.com (Julia Baresch) wrote in message news:<50**************************@posting.google. com>...

I figured out what was causing this - it won't pull negatives in a
field set to currency format. I used my copy of table and form and
changed the format to Long Integer with Standard format, and then it
pulled negatives with no problem.

However, in the meantime I realized if we enter negatives in my
database it will throw off the totals, so we're not going to do that.

Thanks again for your help :-)

Julia