formatting a date within a concatenated field 1

[n2nuk]

Hi there,

I am trying to generate a key based on the clients initials, gender and date of birth. I have produced a key by using the formula below, however the date is presented with commas, is there a way I can remove these?

left({client.forename},1)& left({client.surname},1)&(tonumber(totext({client.DOB},'yyyyMMdd')))&{client.gender}

The field is presented as AL20,081,215.00M, Id like to remove the comma's so that the date part is presented as 20081215.

Many thanks

 

[lbass]

Before the second to the last parenthesis in the tonumber() part of the formula, add: 0,””

The 0 prevents decimals, and the “” indicates no separator (comma).

Please retype this as my iPad font set creates quotes that will cause formulas to fail.

-LB

 

[n2nuk]

Hi Lbass,

I am getting an error "The ) bracket is missing" when I validate. I have entered the text as per your post

left({client.forename},1)& left({client.surname},1)&(tonumber(totext({client.DOB},'yyyyMMdd'))0,"")&{client.gender}

I have even attempted to move the text in between the first and second closing brackets and get the same error.

 

[lbass]

It should be: )0,””))

-LB

 

[n2nuk]

Still no joy!!

I have copied it exactly as described.

I have taken out the other fields to exclude any other factors that may be causing this.

(tonumber(totext({client.DOB},'yyyyMMdd')0,"")) still receiving the same error

 

[lbass]

What datatype is {client.DOB}?

Is the error message the same?

Please post the entire latest version of the formula.

-LB

 

[n2nuk]

Hi Lbass,

Many thanks once again.

the formula is

(tonumber(totext({client.dob},'yyyyMMdd' ))0,""))) the dob field is a date datatype

The full formula with the other fields concatenated to it will look like
left({client.forename},1)& left({client.surname},1)&(tonumber(totext({clientDOB},'yyyyMMdd')))& {client.gender}

Thanks

 

[lbass]

If DOB is a date or datetime, then you can just use:

totext({client.DOB},'yyyyMMdd')

You don't need to use tonumber at all. Also, please note that I was incorrect when I added arguments to tonumber()--those arguments are for formatting numbers being converted to text with totext().

Sorry for the confusion.

-LB

 

[n2nuk]

That's worked a treat, apologies with the confusion with the tonumber syntax.

Many thanks Lbass and for persevering with me!