add charactor if required in query

[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

 

[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";

 

[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

A permanent change?

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

Back up your data first.

 

[MGFoster]

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)

-----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-----