Dealing with leading zeros 1

[straybullet]

Have the following fields

ID_Num - text field with set format - Letter,letter,number,number,letter,number,number,number
ex: EE01A001

ColorCode - number field with set format -
Number,number,number
ex: 012

StyleCode - number field with set format -
Number,number
Ex: 01

Would like to combine the information to concatenate this to a single 13 character identifier (ProductID&ColorCode&StyleCode)

However, ColorCode and StyleCode while formatted as 000 and 00 respectively, will not properly join.

EE01A001 + 012 + 01 should equal EE01A00101201

Instead, its coming out as EE01A001121.

Additionally, if there is no color or style choice to be made, I have been able to get a zero displayed with

CompProdNum: [Products].[ID_Num] & (IIf([Product_ColorChoices].

,[Product_ColorChoices].[Code],0)) & (IIf([Style.StyleNum],[Style.StyleNum],0))

but have not been able to get either the 00 or the 000 to display. (the 00 and 000 automatically change to just 0's)

Any suggestions?

Let them hate - so long as they fear... Lucius Accius

 

[Remou]

Hi
I think you need more formats!
ProductID & Format(ColourCode, "000") & Format(StyleCode, "00")

 

[straybullet]

Can't try it till tomorrow at work but is this what you had in mind?

CompProdNum: [Products].[ID_Num] & (IIf([Product_ColorChoices].

,Format([Product_ColorChoices].[Code],"000"),0)) & (IIf([Style.StyleNum],Format([Style.StyleNum],"00"),0))


Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

well Im lost lol

Set myself up a test db here at home because I couldnt wait to try!

[ProductID] & IIf([ColorID],Format([ColorID],"000"),0) & IIf([StyleID],Format([StyleID],"00"),0)

[ProductID] & (IIf([ColorID],Format([ColorID],"000"),0)) & (IIf([StyleID],Format([StyleID],"00"),0))

[ProductID] & IIf([ColorID],(Format([ColorID],"000"),0)) & IIf([StyleID],(Format([StyleID],"00"),0))

Gonna keep trying - but will also be checking back here for any possible help

Thanks in advance!



Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

OK seems to be working....

[ProductID] & NZ(IIf([ColorID],Format([ColorID],"000"),"000"),"000") & NZ(IIf([StyleID],Format([StyleID],"00"),"00"))

Produces:

EE01A001 + 000 + 00 = EE01A00100000

EE01B016 + 005 + 03 = EE01B01600503


Hadnt thought I could use the "000" with the NZ (thought it had to be just a 0). If anyone could suggest a neater way of achieving this, please let me know.

Let them hate - so long as they fear... Lucius Accius

 

[Remou]

Hi
This is a little shorter, and seems to work:
[tt][ProductID] & Format(Nz([ColorID], 0), "000") & Format(Nz([ColorID], 0), "00")[/tt]

 

[straybullet]

Thank you so much!

Let them hate - so long as they fear... Lucius Accius