data manipulation

[Guest]

Ok, I've thought and thought about this one with no solution.

I have an excel file I need to import daily into my Access program. On the
excel sheet, the "primary key" looks like 00001363149M. I need it to drop
the first three zeros and replace the M with a zero. It should look like
013631490 when it hits my table. Is it better to somehow fix this in excel
before I import or is there a way to do it inside Access (possibly through an
update query).

My project was running smooth until I realized the data did not look the same.

 

[Douglas J. Steele]

It can be done in Access.

Is that string length a constant 12 characters, and it's always an M at the
end?

Mid([ExcelValue], 4, 8) & "0"

 

[Guest]

Yes, it is always 12 characters.

It can be done in Access.

Is that string length a constant 12 characters, and it's always an M at the
end?

Mid([ExcelValue], 4, 8) & "0"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I've thought and thought about this one with no solution.

I have an excel file I need to import daily into my Access program. On
the
excel sheet, the "primary key" looks like 00001363149M. I need it to drop
the first three zeros and replace the M with a zero. It should look like
013631490 when it hits my table. Is it better to somehow fix this in
excel
before I import or is there a way to do it inside Access (possibly through
an
update query).

My project was running smooth until I realized the data did not look the
same.

 

[Guest]

I tried it and it worked great! Thanks!

It can be done in Access.

Is that string length a constant 12 characters, and it's always an M at the
end?

Mid([ExcelValue], 4, 8) & "0"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I've thought and thought about this one with no solution.

I have an excel file I need to import daily into my Access program. On
the
excel sheet, the "primary key" looks like 00001363149M. I need it to drop
the first three zeros and replace the M with a zero. It should look like
013631490 when it hits my table. Is it better to somehow fix this in
excel
before I import or is there a way to do it inside Access (possibly through
an
update query).

My project was running smooth until I realized the data did not look the
same.