Concatenating UniqueID field

J

Jim Aksel

I am attempting to create a 7 digit unique ID field from a formula is a
custom text field (ie: Text1)The specified format:IPTnnnn where IPT is a
three character representation of the file name and nnnn is the UniqueID
number. To maintain 7 characters, I must place leading 0's into the UniqueID
field --- here's my formula:

"SSE"+choose(len([Unique ID]),"000","00","0","")+str([Unique ID])

Where here the file is SSE. The choose function appends the appropriate
number of leading 0's on to the UniqueID so it is always four digits.

The result is a leading space before the UniqueID:
SSE 1345 should be SSE1345
SSE0 144 should be SSE0144
SSE00 12 should be SSE0012
SSE000 3 should be SSE0003

Any ideas?
 
J

Jack Dahlgren

Looks like there is an issue between the output of choose and the UID.
Str([UniqueID]) produces a leading space.

This appears to work:
"SSE" & choose(len([Unique ID]),"000","00","0","") & trim(str([Unique ID]))
 
J

Jim Aksel

Of course. I've had my development hat off too long.
BTW, since the unique ID can approach 99999 I revised to an 8 character field.

"SSE"+choose(len([Unique ID]),"0000","000","00","0","")+trim(str([Unique ID]))
Works great.
Thanks!


Jack Dahlgren said:
Looks like there is an issue between the output of choose and the UID.
Str([UniqueID]) produces a leading space.

This appears to work:
"SSE" & choose(len([Unique ID]),"000","00","0","") & trim(str([Unique ID]))
--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"


Jim Aksel said:
I am attempting to create a 7 digit unique ID field from a formula is a
custom text field (ie: Text1)The specified format:IPTnnnn where IPT is a
three character representation of the file name and nnnn is the UniqueID
number. To maintain 7 characters, I must place leading 0's into the
UniqueID
field --- here's my formula:

"SSE"+choose(len([Unique ID]),"000","00","0","")+str([Unique ID])

Where here the file is SSE. The choose function appends the appropriate
number of leading 0's on to the UniqueID so it is always four digits.

The result is a leading space before the UniqueID:
SSE 1345 should be SSE1345
SSE0 144 should be SSE0144
SSE00 12 should be SSE0012
SSE000 3 should be SSE0003

Any ideas?
Click to expand...
Click to expand...
 
R

Rod Gill

"IPT"&left("0000"&activecell.Task.UniqueID,4)

works in VBA, haven't tried it in a formula, but it should work.

--

Rod Gill
Microsoft MVP for Project
The book on Project VBA http://www.projectvbabook.com


Jim Aksel said:
Of course. I've had my development hat off too long.
BTW, since the unique ID can approach 99999 I revised to an 8 character
field.

"SSE"+choose(len([Unique ID]),"0000","000","00","0","")+trim(str([Unique
ID]))
Works great.
Thanks!


Jack Dahlgren said:
Looks like there is an issue between the output of choose and the UID.
Str([UniqueID]) produces a leading space.

This appears to work:
"SSE" & choose(len([Unique ID]),"000","00","0","") & trim(str([Unique
ID]))
--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"


Jim Aksel said:
I am attempting to create a 7 digit unique ID field from a formula is a
custom text field (ie: Text1)The specified format:IPTnnnn where IPT is
a
three character representation of the file name and nnnn is the
UniqueID
number. To maintain 7 characters, I must place leading 0's into the
UniqueID
field --- here's my formula:

"SSE"+choose(len([Unique ID]),"000","00","0","")+str([Unique ID])

Where here the file is SSE. The choose function appends the
appropriate
number of leading 0's on to the UniqueID so it is always four digits.

The result is a leading space before the UniqueID:
SSE 1345 should be SSE1345
SSE0 144 should be SSE0144
SSE00 12 should be SSE0012
SSE000 3 should be SSE0003

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
J

Jack Dahlgren

Unique ID can exceed 99999,
but you probably don't want to hear about that.

-Jack

Jim Aksel said:
Of course. I've had my development hat off too long.
BTW, since the unique ID can approach 99999 I revised to an 8 character
field.

"SSE"+choose(len([Unique ID]),"0000","000","00","0","")+trim(str([Unique
ID]))
Works great.
Thanks!


Jack Dahlgren said:
Looks like there is an issue between the output of choose and the UID.
Str([UniqueID]) produces a leading space.

This appears to work:
"SSE" & choose(len([Unique ID]),"000","00","0","") & trim(str([Unique
ID]))
--
Jack Dahlgren
Project Blog: http://zo-d.com/blog
Macros: http://masamiki.com/project/macros.htm
"docendo discimus"


Jim Aksel said:
I am attempting to create a 7 digit unique ID field from a formula is a
custom text field (ie: Text1)The specified format:IPTnnnn where IPT is
a
three character representation of the file name and nnnn is the
UniqueID
number. To maintain 7 characters, I must place leading 0's into the
UniqueID
field --- here's my formula:

"SSE"+choose(len([Unique ID]),"000","00","0","")+str([Unique ID])

Where here the file is SSE. The choose function appends the
appropriate
number of leading 0's on to the UniqueID so it is always four digits.

The result is a leading space before the UniqueID:
SSE 1345 should be SSE1345
SSE0 144 should be SSE0144
SSE00 12 should be SSE0012
SSE000 3 should be SSE0003

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Direct access to task's field data (Name) or Lookup using UniqueID 5
Addressing the Task Name field format in Microsoft Project 1
Error when setting Shape.Item to UniqueID, and shape does exist 4
Validation rule, index or neither? 6
VBA Code - Need Help 8
Help with Field addressing in Microsoft Project. 1
Help Fixing Code - Edit Access table from Excel 11
Troubleshooting Lookup Fields 2

Top