Lookup Question regarding Data Import to Access Table

[Guest]

Importing Data from Mainframe DB, including several fields of varying length,
which will include both letters and numbers, ie RCLA-427765541, etc. These
will need to be calculated, so must remain classified as Numbers, rather than
Text Entries, however, cannot find the best way to reference the data in the
Lookup field that is not converting or modifying the number. Have tried
Double, Long Integer, etc. What I need is something that will recognize the
data as entered, but Not as Text, with no justification, or no conversion to
a decimal, (which deletes leading 00's.) Any assistance would be appreciated!

Thanks!

 

[Albert D.Kallal]

Well, since there is text in the data field, then you need to leave it as
text

However, is the number part *always* after the "-" (hyphen) ?

if yes, then it is simple matter to define a long int field, and then run a
update query to "grab" this number into the long int field.

You query you run after would be very simple...like

update tblData set MyIntPart = split([txtField,"-")(1)

Do the above on test data...but the result would be a long int field with
only the number part. So, don't try and mess with the existing field...it
has text and numbers...and thus needs to remain a text field....

(and...test this on a copy of the data...as you usually will mess up the
first few times....)