add charactor if required in query

  • Thread starter Thread starter Alec Green
  • Start date Start date
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
 
Tables have fields, not databases. You can create an update query like

UPDATE tblJobs
SET JobNumber = "J" & JobNumber
WHERE Left(JobNumber,1) <> "J";
 
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.
 
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-----
 
Back
Top