Why zeros are added at start

[#1]

i have different form of medical Numbers ( '2226' , 'MQ234' )

But when i do a concat code such as   Id & '-' & [Medical Number]  the format i receive for the ones 2226 is  0000 2226

how can i make it not do that ???

[its_anandrjs]

Use like

Trim(id) & '-' & [Medical Number]

if you have sample in excel then provide.

and also check that your field id contains only 2226  not 0000 2226 in the field.

[#3]

post some sample data..

[fkeuroglian]

Hi

try this

REPLACE(LTRIM(REPLACE([Medical Number], '0', ' ')), ' ', '0')as [Medical Number],

Good luck

Fernando

[sunilkumarqv]

Mixed of  string and int better to be use Dual Function

dual( 'Id' , 'Medical Number' ) AS YouField

[rajat2392]

use the below code

concat([Medical Number] ,'-')

[#7]

DEar All Iam getting the following table

Medical Record NO.	FilterID
mq00937178	7708-mq00937178-01.17.2013
mq00949291	7708-mq00949291-01.23.2013
mq00969069	7708-mq00969069-12.19.2012
MQ00969106	7708-MQ00969106-12.19.2012
MQ00969309	7708-MQ00969309-12.20.2012
509	7708-00000509-04.25.2013
509	7708-00000509-06.20.2013
598	7708-00000598-03.20.2013
602	7708-00000602-04.25.2013

where i use in the script the following formula

LocationID & '-' & [Medical Record No. :] & '-' & Date as FilterID,

I tried all the solutions mentioned but didnt get

[mambi]

can you provide us a sample data !

[maxgro]

try with

LocationID & '-' & text([Medical Record No. :]) & '-' & Date as FilterID,

[its_anandrjs]

If medical record no which is 00000509 and so with same length like 8 then use below code to make it NUM and then use in the script

LocationID & '-' & [Medical Record No. :] & '-' & Date as FilterID,

Try This

if(len([Medical Record NO.])=8,Num([Medical Record NO.]),[Medical Record NO.]) as [Medical Record NO.]