I'm pulling my hair our with this one!

[Lateral]

Hi guys

I have a database that generates invoices and allows the user to record payments...it works really well.

I decided to create a form that would display all invoices that either had not had any payments made (the total of the invoice was still outstanding) or they had some payments made but there was still some money outstanding....in other words, if there was any money outstanding the invoice would be displayed in this form. (see the attached screenshot01.png)

I have this working fine except that I have an invoice that is fully paid with a balance of $0.00 that is being displayed! (see attached screenshot02.png).

If I click on the $0.00 amount, some really weird numbers come up and I assume that these are causing the problem. (see attached screenshot03.png).

Here is the code I am using to find the "Amount Owing":

=[Parts Total]-[Total Payments]

This is the only invoice that is doing this and I just can't figure out why. I have looked a the tables/records and can't see anything....

Any help will be greatly appreciated.

Regards
Greg

 

[pbaldy]

What are the data types of the fields involved? I'm guessing single or double, which are floating point numbers and can show values like that. I'd use currency.

 

[CJ_London]

as a 'get you by' try

=round([Parts Total]-[Total Payments],2)

 

[Lateral]

Hi Guys

From what I can tell, all fields are Currency.

CJ, I tried your suggestion and although it got rid of the weird numbers, the invoice is still being showed even though it has $0.00 outstanding.

 

[pbaldy]

You'd also want to use the Round() function in the criteria or filter, but I'd like to get to the source of the problem. If you have a calculation feeding a field, you may need to round there.

 

[Lateral]

I'll get some more screen shots and info as I can't explain why all other invoices are working fine and only this one is acting weird.

 

[JHB]

Hi Guys

From what I can tell, all fields are Currency.

Did you actually check it?

 

[Lateral]

Yep. I'll check everything again when I get home in a few hours.

 

[arnelgp]

i suggest changing your field type to Decimal, it is the most accurate among all floating point type. also in your calculation try to cast: CDec(amount1) + CDec(amount2)+ etc...

 

[Lateral]

Ok, I have check the 3 Text Boxes that I am using called "Total Payments", "Parts Total" and "Amount Owing" and they are all "Currency".

The "Total Payments" is fed by a query as it totals up all of the records in the "Payments" table belonging to an invoice, The "Parts Total" is also fed by another query that totals up the parts that are attached to an invoice and the "Amount Owing" simply the uses the following to calculate if any money is owing:

=[Forms]![fAll Valid Workorders Owing Money].[Form]![Parts Total]-nz([Total Payments])

I have checked that the "Payment Amount" field in the "Payment" table is set ot Currency and it is.

I have checked that the field used to calculate the parts $ is set to currency and it is.

I also created a new invoice containing the same two items and it shows exactluy the same issue.

Regards
Greg

 

[Galaxiom]

The Currency in a textbox is just a display Format. Currency datatype in the table fields for anything that can be in parts.

So if you can sell a fraction of something its amounts should also be Currency.

 

[JHB]

Could you post a stripped down version of your database, with that record in your table that cause the problem.

 

[apr pillai]

Make a copy of the tables involved (or better make a copy of the database itself) into a separate database.

Use Update Query to round the value fields involved separately with the rounding formula, like INT([totalvalue] * 100)/100.

Before going for updating the values open the table directly and inspect the values, particularly the Invoice in question. Check after updating also. You may set the formatting property of the fields with the format string 0.0000000 to inspect the value upto 7 digits after the decimal point.

After this check whether the Invoice in question is still showing as outstanding or not.

 

[Lateral]

Hi JHB,

I'll put together a sample database later today.....

Regards
Greg

 

[JHB]

Hi JHB,

I'll put together a sample database later today.....

Regards
Greg

I'll keep an eye on the thread.

 

[Lateral]

Ok, here is a sample database demonstrating the error.

You should be able to simple run it and see the pop up form with the $0.00 Amount Owing. If you click the $0.00 you will see the funny amount....

There is just a single form called "fAll Valid Workorders Owing Money".

I'm curious to know what the problem and solution is...

Thanks again for your help with this.

Regards
Greg

 

[pbaldy]

For starters, this test in the query shouldn't have quotes around 0:

>="$0.00" And <[Parts Total]

A number of the fields in the table are Double, as asked about earlier. Quantity in the parts table, billable hours in the labor table, etc. Gotta go, but a quick test of this in the criteria made the query return no records:

>=0 And <[Parts Total]

 

[Lateral]

Hi Pbaldy,

Thanks for looking into this.

I'm sorry about the confusion regarding the number fields being "double"...I'm a newbie and didn't understand exactly...

I pretty sure that the "$0.00" needs to stay as is otherwise it does not show what it needs to show.

I changed the "Quantity" field in the "Workorder Parts" table from "Double" to either "Long Integer" or "Decimal" and it now works properly!

What should I permanently change these types of fields to?

Thanks again for your help with this issue.

Regards
Greg

 

[pbaldy]

I would use Currency for amount fields. Integer or Long Integer for quantities, unless there can be fractional quantities, in which case Decimal or Currency.

I said there should not be quotes around the zero; the test otherwise is fine. With quotes, you're testing an text value rather than a numeric one.

In any case, glad that changing the data type fixed the problem!

 

[Lateral]

Thanks Paul,

If I remove the ""'s from $0.00 and save the query, Access puts them back!

Anyway, I can now go through my tables and fix this issue once and for all....thanks again for your help and patience.

Regards
Greg