Query won't pull amounts less than zero

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

re: Query won't pull amounts less than zero

-----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:
[color=blue]
> 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[/color]

re: Query won't pull amounts less than zero

"Julia Baresch" <jbaresch@oldrepublic.com> wrote in message
news:505e0ad2.0310211114.4028c1b3@posting.google.c om...[color=blue]
> 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[/color]

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

Doug M

re: Query won't pull amounts less than zero

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" <owner@nowhere.com> wrote in message news:<3f95e18a$0$21654$afc38c87@news.optusnet.com. au>...[color=blue]
> "Julia Baresch" <jbaresch@oldrepublic.com> wrote in message
> news:505e0ad2.0310211114.4028c1b3@posting.google.c om...[color=green]
> > 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[/color]
>
> Is it possible that the negative amounts have been entered as ($500.00)
> instead of -500?
>
> Doug M[/color]

re: Query won't pull amounts less than zero

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

jbaresch@oldrepublic.com (Julia Baresch) wrote in message news:<505e0ad2.0310220537.70835cc8@posting.google. com>...[color=blue]
> 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" <owner@nowhere.com> wrote in message news:<3f95e18a$0$21654$afc38c87@news.optusnet.com. au>...[color=green]
> > "Julia Baresch" <jbaresch@oldrepublic.com> wrote in message
> > news:505e0ad2.0310211114.4028c1b3@posting.google.c om...[color=darkred]
> > > 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[/color]
> >
> > Is it possible that the negative amounts have been entered as ($500.00)
> > instead of -500?
> >
> > Doug M[/color][/color]

re: Query won't pull amounts less than zero

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 :-)

jbaresch@oldrepublic.com (Julia Baresch) wrote in message news:<505e0ad2.0310271428.79fd5e08@posting.google. com>...[color=blue]
> 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[/color]