Access 97 Update query

[Neville MADDEN]

Good morning everyone,

I'm producing an A97 database of Mining records. One of the tables has the
map sheet references as SH/55-14. The other table has the references as
SH55-17. I want to add the "/" to the data in table2 (or take it out of
Table 1). I have tried all sorts of update criteria such
as "*""*"*&"/"&"*""*" and variations on this but none of them want to work.
There are 12,000+ records in the db so to do it manually would be a pain in
the backside.

I'd appreciate it if anyone can help me or offer suggestions.

Thanks

Neville

[Arch]

On 18 Aug 2007 03:13:17 GMT, Neville MADDEN <nr******@yahoo.com>
wrote:

>Good morning everyone,

I'm producing an A97 database of Mining records. One of the tables has the
map sheet references as SH/55-14. The other table has the references as
SH55-17. I want to add the "/" to the data in table2 (or take it out of
Table 1). I have tried all sorts of update criteria such
as "*""*"*&"/"&"*""*" and variations on this but none of them want to work.
There are 12,000+ records in the db so to do it manually would be a pain in
the backside.

I'd appreciate it if anyone can help me or offer suggestions.

Thanks

Neville

That could be done with a simple update query (assuming the data is
always in the format LL##.

UPDATE OtherTable SET
MapRef = Left([MapRef],2) & "/" & Mid([MapRef],3) ;

HTH,
Arch

[fredg]

On 18 Aug 2007 03:13:17 GMT, Neville MADDEN wrote:

Good morning everyone,

I'm producing an A97 database of Mining records. One of the tables has the
map sheet references as SH/55-14. The other table has the references as
SH55-17. I want to add the "/" to the data in table2 (or take it out of
Table 1). I have tried all sorts of update criteria such
as "*""*"*&"/"&"*""*" and variations on this but none of them want to work.
There are 12,000+ records in the db so to do it manually would be a pain in
the backside.

I'd appreciate it if anyone can help me or offer suggestions.

Thanks

Neville

In Access 97 it would be simpler to add the "/" rather than remove it.

Update YourTable Set YourTable.FieldName = Left([FieldName],2) & "/" &
Mid([FieldName],3) Where InStr([FieldName],"/") =0;

In Access 2000 or newer it would have been simpler to remove the "/".

Update YourTable Set YourTable.FieldName = Replace([FieldName],"/","")

no whee clause needed.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail