Character Count

[jamie13]

Hi,
I'm currently using access 97. I have a long list of numbers which were provided for me in a table. Each cell contains from 7-9 digits. It is important that for my task all the cells have exactly 9 digits. Is there any way i can count the characters in a cell and generate a list of all the numbers with less than 9 digits?

Thanks
Jamie

 

[MarrowGenx]

Try the LEN() function within a query and place a contraint of <9. This will show you all of the values less than 9. Hope this helps.

Marrow

 

[jamie13]

fantastik thanks

 

[FirstAndGoal4]

I assume there is a table with many columns here:

Dim fld As Field
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(&quot;your table name here&quot
rs.MoveFirst
Do While Not rs.EOF
For Each fld In rs.Fields
If Len(Trim(fld.Value)) <> 9 Then
'....do something to let you know which record and field is incorrect
MsgBox &quot;Record #:&quot; & rs.AbsolutePosition & &quot;, Field:&quot; & fld.Name
End If
Next
rs.MoveNext
Loop
rs.Close
Set rs = Nothing