Update Query Help

  • Thread starter Thread starter John Roberts via AccessMonster.com
  • Start date Start date
J

John Roberts via AccessMonster.com

Hi
I have a Table with a field named Location (ex. C:\.....) and another Table
with 2 fields named (fromDrive)(ex. C:\) and (ToDrive)(ex. F:\)

I would like to update the 1st 3 positions of the Location with the value
of ToDrive whenever the fromDrive and 1st 3 positions of Location are
equal.

Thanks
 
John said:
Hi
I have a Table with a field named Location (ex. C:\.....) and another Table
with 2 fields named (fromDrive)(ex. C:\) and (ToDrive)(ex. F:\)

I would like to update the 1st 3 positions of the Location with the value
of ToDrive whenever the fromDrive and 1st 3 positions of Location are
equal.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I do not recommend doing this, but, it's your DB...

Just a guess using Access 2000 Replace() function:

UPDATE tableA

SET Location = Replace(Location, Left(Location,3),
(SELECT ToDrive FROM tableB
WHERE FromDrive = Left(tableA.Location,3)))

WHERE Left(Location,3) = (SELECT FromDrive FROM tableB)
====
I recommend applying the ToDrive only when you need to: in a query or
for a display.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnG/34echKqOuFEgEQIWzQCgiry19ds6PCmmmA/vPkvD1GVQg9wAoJQx
PRmoUNeHcypg+v/6zf6k4fz6
=1Cg/
-----END PGP SIGNATURE-----
 
Back
Top