Pad the beginning of a field if not len() = 9 1

[sparkbyte]

I have a simple update that pads the beginning of a field entry with zeros.

I am sure there is a better way to do this.

Here is the current code

'------------------------------------------------------
'------------------------------------------------------
' Updated A File Numbers to 8 digits
'------------------------------------------------------
'------------------------------------------------------

SQLText = "UPDATE tblTrackingParse SET tblTrackingParse.FileNumber = 'A0' & Right([FileNumber],8) " & _
          "WHERE ((([tblTrackingParse].[FileNumber]) Like 'A*'))"

DoCmd.RunSQL SQLText, True

'------------------------------------------------------
'------------------------------------------------------
' Updated A File Numbers to 9 digits
'------------------------------------------------------
'------------------------------------------------------

SQLText = "UPDATE tblTrackingParse SET [tblTrackingParse].[FileNumber] = 'A' & Mid([FileNumber],2,8)" & _
        " WHERE ((([tblTrackingParse].[FileNumber]) Like 'A00*'))"

DoCmd.RunSQL SQLText, True

Problem I have is this.

The current code only deals with one apha prefix.

FileNumbers can be.

A123456789
A12345678

S123456789
S12345678

T123456789
T12345678

W123456789
W12345678


But there are also reciept file numbers that are formated like...

[3 alpha character site code][10 numeric]

So a reciept number would look like,

ABC1234567890

---------------

The logic I believe would be

if prefix = [a-zA-Z][a-zA-Z][a-zA-Z] then skip
if length is less then 9 then look at first character.
if 1st char = IN (A,S,T,W) then pad after the (A,S,T,W) pad the needed zeros.


Just not sure how do do this as VBA.




Thanks

John Fuhrman

 

[danvlas]

Try this SQL code:

declare @code varchar(10)
select @code='A123411112'
--Expression can become the definition of the calculation
select
	case 
		when ascii(substring(@code,3,1))> 65 then @code --If the 3-rd char is a letter, return the code
		when ascii(substring(@code,2,1))> 65 then 'Second char LETTER' --If the 2-nd char is a letter, show error text
		when len(@code)<10 then left(@code,1) + replicate('0', 10-len(@code)) + right(@code, len(@code)-1) --pad with zeroes
		else 'Too long: ' + cast(len(@code) as varchar(3)) + ' CHARS' 
	end 
--Expression end

Replace @code with the name of your column, then try:

Select ColumnName, {Expression} As Padded
From YourTable


Daniel Vlas
Systems Consultant

 

[sparkbyte]

That looks great! Thanks!!



Thanks

John Fuhrman

 

[sparkbyte]

Oh BTW, is there a faster way to make sure all letters are upper case??

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

UPDATE tblTrackingParse SET tblTrackingParse.FileNumber = Upper(tblTrackingParse.FileNumber)

go

UPDATE tblTrackingParse SET tblTrackingParse.FileNumber = 
    case 
        when ascii(substring(FileNumber,3,1))> 65 then FileNumber --If the 3-rd char is a letter, return the code
        when ascii(substring(FileNumber,2,1))> 65 then 'Second char LETTER' --If the 2-nd char is a letter, show error text
        when len(FileNumber)<10 then left(FileNumber,1) + replicate('0', 10-len(FileNumber)) + right(FileNumber, len(FileNumber)-1) --pad with zeroes
--        else 'Too long: ' + cast(len(FileNumber) as varchar(3)) + ' CHARS' 
    end 
FROM tblTrackingParse

Thanks

John Fuhrman

 

[sparkbyte]

Star for the Help!!


Thanks

John Fuhrman

 

[danvlas]

You're welcome (to the MS SQL world) !




Daniel Vlas
Systems Consultant