tdetzel said:
Gang ... I have to import a big table that includes a numeric field in COBOL format. Negative integers have a special character in the rightmost column of the field.
Is there a way to filter this using an import specification so that I get a standard numeric field in Access?
The COBOL characters are ]=-0; j=-1, k=-2, l=-3, m=-4, n=-5, o=-6, p=-7, q=-8, and r=-9.
Any ideas you have are welcome.
I'm a beginner with access so don't know how to incorporate a filter at import time but you could use an Update query to create a new field by using access string handling functions.
Assume the field that contains your COBOL data is called Field1 then:
Once you have imported the data into a table - create an update query that utilises the following:
IIf(Right$([Field1],1)="[",0,Asc("i")-Asc(Right$([field1],1)))
this returns the correct -1, -2,etc from your righthand column
Mid$([Field1],1,(Len([field1])-1))+[field2]
this returns the left part of the field no matter how long it is
All you need to do then is join the two together in a single statement to create a field (or update your original value) of the form "12345-5" or "123-9"
Note: This only works if you import the numeric string from COBOL as a text field.
Don't know if this helps but it might be worth a try - copy the imported table before trying it, then if it goes wrong you still have a good copy to play with.
Good luck
Gareth
