Import and manipulate data records into access

[Santa-D]

I've got an excel sheet that I need to import into a table, however, I
want the function to automatically manipulate the data prior to it
being imported.

For example, i have a field called [Flt No] and the format is 16107 for

example, when the VBA function is running I want it to add "EA" to the
record so it becomes EA16107 but the field I want it to import into is
called [ORDNO]


another field in the same dataset I want to import is [Product], this
field has many different product descriptions which I want to
manipulate prior to being imported.


I want to convert the following:


Premium UL - PULP
ULP - ULP
Ultimate - PULP
Unleaded Motor Spirit - ULP
LPG - LPG
Autogas - LPG
Monthly AD - Monthly AD


Any help would be wonderful, ps: I'm still an amateur in VBA

 

[Arvin Meyer [MVP]]

It would be far easier to first import the data, then manipulate is with a
series of update queries. For instance the first update query would look
something like:

UPDATE tblMyData SET tblMyData.ORDNO= "EA" & [ORDNO]
WHERE (((tblMyData.ORDNO) Is Not Null And Left([ORDNO],2)<>"EA"));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Santa-D]

Hi Arvin,

Thank you for your response.
I'll give it a go and see how I'll go and let you know of the outcome.

It would be far easier to first import the data, then manipulate is with a
series of update queries. For instance the first update query would look
something like:

UPDATE tblMyData SET tblMyData.ORDNO= "EA" & [ORDNO]
WHERE (((tblMyData.ORDNO) Is Not Null And Left([ORDNO],2)<>"EA"));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I've got an excel sheet that I need to import into a table, however, I
want the function to automatically manipulate the data prior to it
being imported.

For example, i have a field called [Flt No] and the format is 16107 for

example, when the VBA function is running I want it to add "EA" to the
record so it becomes EA16107 but the field I want it to import into is
called [ORDNO]


another field in the same dataset I want to import is [Product], this
field has many different product descriptions which I want to
manipulate prior to being imported.


I want to convert the following:


Premium UL - PULP
ULP - ULP
Ultimate - PULP
Unleaded Motor Spirit - ULP
LPG - LPG
Autogas - LPG
Monthly AD - Monthly AD


Any help would be wonderful, ps: I'm still an amateur in VBA