Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 24th, 2006, 06:21 PM
Authorized User
 
Join Date: Sep 2006
Location: , , Australia.
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote
  #2 (permalink)  
Old October 25th, 2006, 06:28 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old October 25th, 2006, 06:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old October 27th, 2006, 09:47 AM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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-
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:29 PM.


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