Help with leading-zero-data-matching Db1 vs Db2 both MF DLs.

E

EagleOne

2003


I am moving my data from Excel to Access because I am learned of the magic of the "Join"

Saying that, I need to "adjust" data fields in Table1 vs Table2.

In Tables 1 & 2, are DLs from MFs, leading zeros are maintained on one but not the other. In
Table2, leading zeros are stripped.

Therefore, I want to add a column in Table1 that contains a stripped version of BCN. Thus table1
will contain 1) BCN (Original) and 2) BCN (LTrimmed).

In Excel, the formula used was an ArrayFormula:

{=MID(V2,MATCH(TRUE,MID(V2,ROW(INDIRECT("1:"&LEN(V2))),1)<>"0",0),255)}

How can I accomplish the same result in Access? There must be a workaround???


BCNOrig BCNTrim

0068870 68870

A corollary, will Access force me to import numbers as numbers when the data was stored/manipulated
as text? How do I best handle treating a field sometimes as a number (if Access forces me to do so)
but also as text during a compare?

TIA EagleOne
 
P

Pete D.

If you import the number as text it will maintain the leading zeros. If you
change the field to number it will strip the leading zeros. Question is
what is the end result you want, with or without leading zeros and then make
access import it that way. Quick cheat is to pad first few rows in excel
with letters then all the numbers following will also be imported as text.
 
E

EagleOne

Thanks for you thought.

The problem is that much of the data items has characters data in it. Access will not then strip
the leading zeros. Any other thoughts?
 
E

EagleOne

I should have included more data examples:


BCNOrig BCNTrim

0068870 68870
0007800 7800
01AF024 1AF024


I realize that I could use Replace() but I can only remove the leading zeros
not the zero in 1AF024 above.
 
J

javier

Pete D. said:
If you import the number as text it will maintain the leading zeros. If
you change the field to number it will strip the leading zeros. Question
is what is the end result you want, with or without leading zeros and then
make access import it that way. Quick cheat is to pad first few rows in
excel with letters then all the numbers following will also be imported as
text.
Click to expand...
 
C

Clif McIrvin

I should have included more data examples:


BCNOrig BCNTrim

0068870 68870
0007800 7800
01AF024 1AF024
Click to expand...

EagleOne, here's a function I wrote for a slightly different situation
but can be modified to trim leading zeroes:

Public Function xTrim(ByVal str As String) As String
' Strip leading and/or trailing spaces, double quotes (")
' and data literal (#); do not change original string

Dim xlist As Variant, xchar As Variant
xlist = Array("""", "#") 'build list of chars to strip

str = Trim(str) 'remove any leading or trailing spaces
For Each xchar In xlist
Do While Left(str, 1) = xchar 'leading character
str = Mid(str, 2)
Loop
Do While Right(str, 1) = xchar 'trailing charcter
str = Left(str, Len(str) - 1)
Loop
Next xchar
xTrim = Trim(str)
End Function

---------- end sample code ------------

This function goes into a code module, and can be called pretty much
anywhere you can use an expression.

For instance, assuming that you are using a calculated field in a Query
to generate your BCNTrim field it might look like this:

BCNTrim: xTrim(BCNOrig)

HTH
 
P

Pete D.

Okay, it seems to be an import problem, why not keep it a text field and
leave the zeros so you can keep the files linked?
 
E

EagleOne

Thanks for you time and info. Table2 has the zeros already stripped. Therefore to link for a Union
or Join I must adjust on Tables's data.
 
E

EagleOne

Outstanding! Thanks.

My solution was a bit of hope (no more than "0000" leading) and longhand:

IIf(Left([CHOOSEData]![BCN],4)="0000",Mid([CHOOSEData]![BCN],5,255), _
IIf(Left([CHOOSEData]![BCN],3)="000",Mid([CHOOSEData]![BCN],4,255), _
IIf(Left([CHOOSEData]![BCN],2)="00",Mid([CHOOSEData]![BCN],3,255), _
IIf(Left([CHOOSEData]![BCN],1)="0",Mid([CHOOSEData]![BCN],2,255), _
[CHOOSEData]![BCN]))))


I like your's so much better!
 
Top