Hey all

i have a feild that is a string. it needs to be a string for reasons i cant be bothered explaining.
the field is called order number. and when a new record is opened on the form, the previouse order number string is taken, converted into an integer, incremented by 1, then converted back to a string and inserted into the new record. this all works fine.... but

when a new year clicks over i want the order numbers to start again from 0001. the problem is when the string gets converted to an int then converted back i loose all the zeros at the start. is there any way to avoid this?

any help would be appreciated

Hi,

   Here is the flow control. This should get you there.

'------Code Starts-----------
'Your initial value
s = "0001"

'Convert to Integer
i = CInt(s)

'Add 1 to integer
i = i + 1

'Set the length of the string
l = 4

'Set the initial value of the string variable
str = CStr(i)

'Loop through the string variable adding leading 0 until length = l
Do Until Len(str) = l
    str = "0" & str
Loop

'Print Screen
WScript.Echo str

'-------Code Ends-------------

HTH

mmcdonal

I should point out that this is VBScript, but it will work the same in VBA. VBScript has typeless variables, so you don't tell them they are a string, like:

Dim str As String

VBScript figures out what you want. That being said, you still need to use CStr() and CInt() to force the wrong kind of value into your variables in VBA.

HTH

mmcdonal

I can do that in one swell foop (if you like slightly obfuscated code):

       str = right(string(n,"0") & trim(str(val(s)+1)),4)

where s is your original string and n is the desired length.

Note that when you use the str function to convert a number to a string it will return a string with a leading blank (hence the trim function).

-Phil-