add charactor if required in query

A

Alec Green

Hi, I have a database that has a field called Job Number, the data contained
in it is either 12345 or J12345,

I want a query that add a J to the job number if it is not present, so all
the data is the same. (12345 to J12345)

Thanks for your help

Alec
 
D

Duane Hookom

Tables have fields, not databases. You can create an update query like

UPDATE tblJobs
SET JobNumber = "J" & JobNumber
WHERE Left(JobNumber,1) <> "J";
 
F

fredg

Hi, I have a database that has a field called Job Number, the data contained
in it is either 12345 or J12345,

I want a query that add a J to the job number if it is not present, so all
the data is the same. (12345 to J12345)

Thanks for your help

Alec
Click to expand...

A permanent change?

Update YourTable Set YourTable.[FieldName] = IIf(Left([FieldName],1)
= "J", [FieldName],"J" & [FieldName]);

Back up your data first.
 
M

MGFoster

Alec said:
Hi, I have a database that has a field called Job Number, the data contained
in it is either 12345 or J12345,

I want a query that add a J to the job number if it is not present, so all
the data is the same. (12345 to J12345)
Click to expand...

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

Probably a simple UPDATE:

UPDATE table_name
SET job_number = 'J' & job_number
WHERE job_number LIKE '[0-9]*'

The WHERE clause translates to: "where the job_number's first
character is one of the numeric characters zero thru nine."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQheNRoechKqOuFEgEQJB+gCfaLGQZk0DJyUH1kQ+xC41H4zpqyAAoItq
rzjOxxjW9U7L2zZG2wtwJXZn
=T7o/
-----END PGP SIGNATURE-----
 
Top