# Find error in currency feild 2

Status
Not open for further replies.

#### Moss100

##### Technical User
Hello,

I have added up all payments in a table and get a sum that end in 3 decimals.

Obviously there must be a typo in there somewhere.

Initially I have formatted the currency field in the table to show 3 decimal places. My intention then was to use the Right function to select the 3rd decimal digit and then find anything that wasn’t a zero.

The problem is that Access ignores the third decimal, so it returns a number on all results.

Is there any code that has been written or has anyone a solution to find typo errors in currency fields?

So in essence there must be one or more records that have a value at the 3rd decimal place - i.e. £50.238

It's a large table (200000+ records) so doing it manually would be a long job.

Many thanks Mark

Can't you just write a simple Select statement where you convert Payment field(?) to text/string, find the position of a period and count the number of characters after the period

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Thank you for your help (again!).

So I have converted text - field is called DEBIT

How do I find the position of a period and count the number of characters after the period. I really want to identify any string that has 3 or more characters after the period.

Thank you Mark

Hint:
Code:
``````Option Explicit

Sub test()
Dim strNumber As String

strNumber = "50.238"

Debug.Print "Period at position:                " & InStr(strNumber, ".")
Debug.Print "Digits after the period:           " & Mid(strNumber, InStr(strNumber, ".") + 1)
Debug.Print "Number of digits after the period: " & Len(Mid(strNumber, InStr(strNumber, ".") + 1))

End Sub``````

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

You can filter the table, in advanced filter:
- field: [tt][TestedField]<>Round([TestedField],2)[/tt]
- criteria: [tt]TRUE[/tt]

It's for manual correction, if the number of wrong entries is limited. However, it gives an opportunity to verify, what is wrong.

combo

Thank you both. Superstars

So, what did you do? What did you find with your DEBIT field? What was the problem?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

There was a typo - somethimg like £4.856

I manually found it in the end - took 2 hrs - thank you for all your help - Mark

Your field containing the money is declared as text

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Status
Not open for further replies.

Replies
5
Views
162
Replies
11
Views
144
Replies
5
Views
119
Replies
6
Views
167
Replies
11
Views
194