Wrox Programmer Forums Integer Problem
 | Search | Today's Posts | Mark Forums Read
 Access VBA Discuss using VBA for Access programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
October 24th, 2006, 06:21 PM
 Authorized User Join Date: Sep 2006 Location: , , Australia. Posts: 22 Thanks: 0 Thanked 0 Times in 0 Posts
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

October 25th, 2006, 06:28 AM
Friend of Wrox
 Points: 9,611, Level: 42
 Activity: 0%

Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Hi,

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

'------Code Starts-----------
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
October 25th, 2006, 06:31 AM
Friend of Wrox
 Points: 9,611, Level: 42
 Activity: 0%

Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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
October 27th, 2006, 09:47 AM
 Authorized User Join Date: Jul 2006 Location: Boston, MA, USA. Posts: 70 Thanks: 0 Thanked 0 Times in 0 Posts

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-

 Similar Threads Thread Thread Starter Forum Replies Last Post How knowing it is an integer? rtr1900 Classic ASP Basics 1 October 5th, 2006 07:40 AM testing integer rjonk XSLT 2 July 27th, 2006 03:11 PM Integer Size Miquella C++ Programming 7 November 6th, 2005 10:31 PM integer overflow partaola Beginning VB 6 2 March 17th, 2004 09:01 AM

 Contact Us - Wrox - Privacy Statement - Top