find / replace

[Rockape]

Hi all,

Help needed.

I have a table A with a field with names.

I have an excel file with old names (similar to table A)with the corresponding new name.

I would like to somehow import the excel table into the access database as a table (no problem with that) and then replace the old names in Table A with the new new names.

I would prefer it done using some form of VBA module.

Grateful for any assistance.

 

[Rabbie]

in VBA you can try using the fieldname property. Check it out in VBA help. Also check out Tabledef

 

[Rockape]

Further assistance required?

 

[Brianwarnock]

This is a simple update query of the form

UPDATE Table1 INNER JOIN Table2 ON Table1.flda = Table2.flda SET Table1.flda = [table2].[fldb];

Brian

 

[Rockape]

thanks.

worked a treat. Just what i needed. no need for lookups!

thanks again

 

[Rockape]

Hi,

Previous script worked a treat.

Is it possible to update multiple fields using the same update query

I've tried this:

UPDATE Table1 INNER JOIN Table2 ON Table1.flda = Table2.flda
SET Table1.flda = [table2].[fldb],Table1.fldc = [table2].[fldd]

is this possible?

 

[Rabbie]

Hi,

Previous script worked a treat.

Is it possible to update multiple fields using the same update query

I've tried this:

UPDATE Table1 INNER JOIN Table2 ON Table1.flda = Table2.flda
SET Table1.flda = [table2].[fldb],Table1.fldc = [table2].[fldd]

is this possible?

I have done very similar updates so it should be possible. Why not try it and see?

 

[Rockape]

am i missing colons or semi colons?

 

[Rabbie]

am i missing colons or semi colons?

My query has a trailing semicolon at the very end.

 

[Rockape]

Hi,
I've tried with no luck.

I'll keep on trying. Thanks for your comments.

Kind Regards

 

[Rabbie]

Sorry to hear you've had no luck. What exactly is the error message. Are you running it as a free standing query or from SQL? Can you post your exact SQL.

 

[Brianwarnock]

The trailing ; is not required but if you design the query in the design grid it, like excess() is put in by the system.
The query as shown will definitely work, unless it was an afterthought and the original has been run updating flda and thus no matches will occur.

Change the join to ON Table1.flda = Table2.fldb


Brian

 

[barlee]

is it possible to use a script or query to do a global update to a group of macros to change (find and replace) a file name for a text file import?

 

[Brianwarnock]

This is an old "closed" thread and many people will not look at it, I would suggest that you start a new thread with your question which has nothing in common with the original question. I am not sure which forum it belongs in but I would probably post in the General forum.

Brian