Concatenation of Numeric Fields

[acr1]

Hi
Having found these forums very useful I have a question. I have a large Job number range (35000001, 35000002, etc). I need to concatenate this with an item number (10,20,30,etc)on the same line as goes 1-10, 1-20,2-30, etc). I appreciate that I need to convert the numeric to text so that I can cocatenate them both. Their needs to be an allowance for the job number incrementing and obviously expanding to the left..e.g. (35000001, 35000999, etc).

Any assistance would be greatly appreciated.
TIA, Angus

 

[paulmarr]

Angus,

It is not clear whether you want the item number on the front or the back of the Job number but here it is:

ToText(JobNumber,0,"&quot+ToText(ItemNumber,0,"&quot or in reverse if you want them to appear the other way around.

It doesn't matter how big or small the numbers are.

Hope this helps


paulmarr@arcom.com.au

 

[acr1]

Paul
Thanks for your help with the 'ToText' function...however I need to slice off the first 4-5 digits
of the number...e.g. 35000001 would become 0001 and cant find a trim function that works..properly.

I realise this is being sent direct but should now put thru the forum.

Regards
Angus

 

[Ngolem]

then this formula should work for you...as long as you only want the last 4 characters of the Job number


right(totext(table.Jobnumber,0,"&quot,4) + "-" +
totext({table.ItemNumber},0,"&quot

Jim Broadbent

 

[acr1]

Jim
Yes I discovered 'right' and have it setup as per your syntax. Will have to increase the characters after '9999' though.
Many Thanks
Angus

 

[Ngolem]

You are changing the rules on me...

what number of zeros to the left of the Job Number can you have??

Does your Job number increase sequencially?? or can it vary all over the map?

let us say you want to increase the number only when the Job number increases past 9999 for the right 4 digits than you might try this

If tonumber(mid({table.jobnumber},3,2)) = 0 then
right(totext(table.Jobnumber,0,"&quot,4) + "-" +
totext({table.ItemNumber},0,"&quot
else if tonumber(mid({table.jobnumber},3,2)) > 10 then
right(totext(table.Jobnumber,0,"&quot,5) + "-" +
totext({table.ItemNumber},0,"&quot
else
right(totext(table.Jobnumber,0,"&quot,6) + "-" +
totext({table.ItemNumber},0,"&quot
;

with this you can handle 999999 Jim Broadbent

 

[acr1]

Jim
Thanks for your help and code.Yes it increases sequentially.
Regards
Angus