Incorporating an input mask into an append query

[TomIII]

Hello All...

Here's the scenario:

In a widely distributed Access2000 runtime application with separate front and back ends there is an SSN field on a data entry/edit form. The input mask is 000\-00\-0000;;_

On this same form, there is a selection dropdown that displays LName, FName, DOB and SSN. The SSN field is formatted in the related query as: SocSec#: Left([SSN],3) & "-" & Mid([SSN],4,2) & "-" & Right([SSN],4)

Data manually entered into this application displays and reports fine.

Enter the problem: Legacy data imported into the database in the form of a 9-character (123456789) string and appended to the table via an append query displays fine in the SSN data entry/editing field (i.e. 123-45-6789). However, on the dropdown, it shows up as 123--4-6789.

No matter how I manipulate trims, left/mid/rights, etc on the append query...and even as it shows up correctly formatted in the data field...the drop down is still skewed in one direction or the other with a missing digit and added hyphen.

Anybody know of a way of incorporating the input mask into the append query? Would this solve the problem? Alternative suggestions? I'd like to stay away from changing the structures of the front or back ends as this would prove a logistical nightmare. I've got a small application for importing and appending the data and would like the process written into that.

Any assist appreciated.

Thanks...

 

[Steve101]

Tom,

Have you written a little program to iterate through the offending field(s) data written by the mainframe. Its just possible that its "added" some invisible gremlin characters in that 5th position which are causing the problem in relation to the mask, but not being exposed otherwise.

Suggest you try iterating through the field, and examining the data; for example:

'open a recordset on the data (I'll call it rs)
'.....
'.....

'navigate to an offending record
'.......
'.......

debug.? rs!NaughtyField
debug.? len(NaughtyField)
for i = 1 to len(rs!NaughtyField)
ThisChar = mid(rs!NaughtyField,i,1)
debug.? asc(ThisChar) & " " & ThisChar
next i
stop

Then inspect the immediate window, and make sure that the results that you see, are what you expect. Things to look out for; an unexpected fieldlength; ASCII field values that fall outside of the printable character range; for example, 0 (empty character), 27 (excape), and so on.

Hope that this approach yields something,
Cheers,
Steve