Table issue with text format

[kwooten]

I have a table that is imported to Access with a 5 digit identifier code but the leading zero is dropped but access still counts the dropped zero as a value so 01234 looks like _1234. I am linking to a table that has the leading zero removed making it a 4 digit code for any number with a leading 0 and a 5 digit code for any number without the leading zero. what can i do to get these fields to match? The imported file cannot be fixed to include a leading zero.

[JoeM]

I have a table that is imported to Access with a 5 digit identifier code but the leading zero is dropped but access still counts the dropped zero as a value so 01234 looks like _1234

I have never heard of such a thing.
What is the data type of the field in the Access table?
Is there any special formatting on it?
How are you importing that file?

Note that in order to link any two fields together in Access, they will need to be the same data type (in this case, either both Text or both Numbers).

[kwooten]

both fields are text fields in Access. There isnt any special formatting that i am aware of. The file is imported through a program called Monarch. It may be that the file within Monarch is pulling 5 digits but the report may have some instances of 4 digits and the export is picking up that blank field so it puts a space before the 4 digit number...

[orange]

I agree with Joe, haven't heard of this action. Where did this data get imported from?
Can you show us somw sample from the original file?

[kwooten]

yeah i will see if i can get a sample together

[orange]

I've never heard of Monarch, but found this via Google
http://excelwithmonarch.com/tips/mon...gramming-class

[JoeM]

Yes, if it is a Text field, Access would not be dropping the leading zero. It is getting dropped before then.

In order to compare the two files, you will need to add the missing leading zero in. There are a few ways of doing that:
1. Updating the Access table after import (using an Update Query)
2. Adding the leading zero in via a calculated field in a Query, and link this query to your other table

If doing option 1, create a query that select all ID fields where the length is 4. Then update it like this (in the Update To row):

"0" & [IDField]

If doing option 2, use a calculated field like this:

RealID: IIf(Len([IDField])=4,"0","") & [IDField]

[kwooten]

thanks Joe.. ill give that a try