Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find error in currency feild 2

Status
Not open for further replies.

Moss100

Technical User
Aug 10, 2004
586
GB
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 [ponder]

---- 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
 
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 [ponder]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top