Non-cents query

[JimN]

Calculations for all costs are rounded up to the next dollar on our invoices.
The business office personel inputing payments have entered dollar amounts,
like $109.49 which should be $110.00. There are many payments entered in the
table, but few of these payments entered have the odd cents. Is there an
algorithm that can be used in a query to show only those payments that
contain the odd cents?

I have since added a validation rule to the payment field, but need to find
all the erroneous payments for adjustment and finger pointing.

 

[John Vinson]

Calculations for all costs are rounded up to the next dollar on our invoices.
The business office personel inputing payments have entered dollar amounts,
like $109.49 which should be $110.00. There are many payments entered in the
table, but few of these payments entered have the odd cents. Is there an
algorithm that can be used in a query to show only those payments that
contain the odd cents?

I have since added a validation rule to the payment field, but need to find
all the erroneous payments for adjustment and finger pointing.

Use a criterion of

[Amount] <> Fix([Amount])

To round them all up en masse, use this criterion and make it an
Update query; update [Amount] to

Fis([Amount]) + 1

John W. Vinson[MVP]

 

[JimN]

Sorry John, I must not have been clear. The invoice costs vary from invoice
to invoice based on the amount of time spent on a project. I do not want to
change the payment amounts even though they are wrong, I just want the query
to show those payments that have the odd cents like $354.49 as opposed to the
majority of payments entered that are like $112.00. So the query would show
the $354.49 payment, but not the $112.00.

Calculations for all costs are rounded up to the next dollar on our invoices.
The business office personel inputing payments have entered dollar amounts,
like $109.49 which should be $110.00. There are many payments entered in the
table, but few of these payments entered have the odd cents. Is there an
algorithm that can be used in a query to show only those payments that
contain the odd cents?

I have since added a validation rule to the payment field, but need to find
all the erroneous payments for adjustment and finger pointing.

Use a criterion of

[Amount] <> Fix([Amount])

To round them all up en masse, use this criterion and make it an
Update query; update [Amount] to

Fis([Amount]) + 1

John W. Vinson[MVP]

 

[John Vinson]

Sorry John, I must not have been clear. The invoice costs vary from invoice
to invoice based on the amount of time spent on a project. I do not want to
change the payment amounts even though they are wrong, I just want the query
to show those payments that have the odd cents like $354.49 as opposed to the
majority of payments entered that are like $112.00. So the query would show
the $354.49 payment, but not the $112.00.

The criterion I suggested:

WHERE [Payment] <> Fix([Payment])

which you can enter on the query grid by simply typing

<> Fix([payment])

under the Payment field (using your own fieldname of course) on a
Select query will do exactly that, and will not change anything in
your table.

John W. Vinson[MVP]

 

[JimN]

Thanks John, it worked and i have the 9 records out of 6000+.
Jim

Sorry John, I must not have been clear. The invoice costs vary from invoice
to invoice based on the amount of time spent on a project. I do not want to
change the payment amounts even though they are wrong, I just want the query
to show those payments that have the odd cents like $354.49 as opposed to the
majority of payments entered that are like $112.00. So the query would show
the $354.49 payment, but not the $112.00.

The criterion I suggested:

WHERE [Payment] <> Fix([Payment])

which you can enter on the query grid by simply typing

<> Fix([payment])

under the Payment field (using your own fieldname of course) on a
Select query will do exactly that, and will not change anything in
your table.

John W. Vinson[MVP]