Format in Dlookup function

[tpcervelo]

I am trying to populate a field on a form by concatinating two fileds from the first record of a table. I put the following in the fields Control Source.
=DLookUp("[ShpOrdNbr] & '-' & [ShpOrdSuf]","SOKey","ID = 1")
This works but the second field is a number and I would like to zero fill it so it shows as example 50553-003 instead of 50553-3. I tried to use the Format function but whatever I try as far as syntax causes an error.
Any help is much appreciated.
thanks...

[michaeljohnh]

Off the top of my head it sounds like you could use the Len function to check the length of the second field. If the result is 1 then concatonate "00" ; if the result is 2 the concatonate "0".

[NTC]

This is of course not a number. It is a text field that just doesn't use alpha characters.

It is a little confusing because first you say 'it works' but then "would like to zero fill it so it shows as example 50553-003".....

But if you are saying that you must convert say 3 to 003 in order to make it work then mj's advice is the correct idea to follow....

[slave138]

This code worked for me in a test DB:

=DLookUp("[ShpOrdNbr] & '-' & Format([ShpOrdSuf],'000')","SOKey","ID = 1")

Table built under these assumptions:
ShpOrdNbr - Number
ShpOrdSuf - Number
ID - Auto Number

[slave138]

Sample attached

[tpcervelo]

thanks slave138...that worked. I thought I had tried encasing it in parens but obviously not.
thanks again...

[slave138]

My pleasure