p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Integer Problem (http://p2p.wrox.com/showthread.php?t=49400)

needelp October 24th, 2006 06:21 PM

Integer Problem
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

mmcdonal October 25th, 2006 06:28 AM


   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

'Print Screen
WScript.Echo str

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



mmcdonal October 25th, 2006 06:31 AM

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.



pjm October 27th, 2006 09:47 AM

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).


All times are GMT -4. The time now is 04:21 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.