Solved

Trying to increment a text "number" by 1

Posted on 2014-10-21
15
150 Views
Last Modified: 2014-10-22
I have a field on a form which looks like a number but is actually a text field and has to stay that way.  I'm trying to increment that "number" by "1" if the form is on a new record.

Here's my code which is not working.

    If Me.NewRecord Then
        Me.txtLastInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice")
        Me.txtLastInvoiceN = CInt(Me.txtLastInvoiceID)
        Me.txtPOINVInvoiceID = Me.txtLastInvoiceN + 1
    End If

Open in new window


Me.txtLastInvoiceID  is the last invoice number which is really a text field that looks like a number
Me.txtLastInvoiceN  is my attempt to convert it to a real number
Me.txtPOINVInvoiceID  is my attempt to increment Me.txtLastInvoiceN   by "1"

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395072
try

Me.txtPOINVInvoiceID = Cstr(Me.txtLastInvoiceN + 1)
0
 

Author Comment

by:SteveL13
ID: 40395149
Didn't work.  I get an "Overflow" message on this line when I replace the line with your code....

Me.txtLastInvoiceN = CInt(Me.txtLastInvoiceID)
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40395212
Use long instead of integer.

Me.txtLastInvoiceN = CLng(Me.txtLastInvoiceID)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395217
or

Me.txtPOINVInvoiceID= DMax("[PO INV Invoice ID]", "PO Invoice") + 1
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395221
<Didn't work.  I get an "Overflow" message on this line when I replace the line with your code....
>

it is your code causing the error, not mine..
0
 

Author Comment

by:SteveL13
ID: 40395255
I realize that.  Now using this...

    If Me.NewRecord Then
        Me.txtLastInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice")
        Me.txtLastInvoiceN = CStr(Me.txtLastInvoiceID)
        Me.txtPOINVInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice") + 1
    End If

I get Runtime error  "The field is too small to accept the amount of data you attempted to add..."

The text field is limited to 16 characters.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395270
<The text field is limited to 16 characters. >

then you need to revise this to accommodate  more characters..
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40395331
If you have a text field which is too long to be converted to a number, then you have to back to your first grade math and do it column by column right to left remembering to carry.
0
 

Author Comment

by:SteveL13
ID: 40395345
If the Me.txtLastInvoiceID  is 1234567812345678 (16 characters), then the next one would be 1234567812345679 (still 16 characters).

I'm sorry if I'm missing something.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395355
this is the first time i am seeing an invoice number that long... :-(
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40395404
Create a loop that returns one character at a time, right to left.  So it starts with position 16.  I'm not going to write the code but here's an idea for the "inside".  I wasn't kidding about implementing first grade arithmetic.  Remember, line up the numbers right aligned on your paper.  Add the two numbers in column 16, if the sum is 10, write down 0 and mark a 1 as the carry above column 16=15.  Then add the carry plus the number plus 1.  And build the result as a string in a separate work area

WorkAccum = char(I) + WorkCarry +1
If WorkAccum > 9 Then
    WorkAccum = 0
    WorkCarry = 1
Else
    WorkCarry = 0
End If
ResultString = WorkAccum & ResultString

Because you are only adding 1 to each digit, your result won't ever be > 10 so you can take some shortcuts in the code.  I'm also assuming that you are not already at the max range of the digits now - 9999999988888889 because you would then have to worry about rolling over into the 17th digit or wrapping around.

BTW, this is how heats are calculated if you've ever worked in an application that tracked the metal composition of parts like the fins in jet engines.  Heats (unique numbers assigned by the foundry so you could ultimately track all parts that were produced with a particular batch of an alloy) are letters so instead of working with base ten, you are working with base 26 as you add 1 to  "thdz" to change z to a and carry 1 to the next column to the left so d becomes e, etc. and we end up with "thea".  If a blade in a jet engine fails, all other parts made from that same heat may need to be recalled and tested to ensure they don't have the same weakness.  Foundries were making alloys long before computers.  How would they know that it would be such a pain for a computer to come up with the next heat number.
0
 

Author Comment

by:SteveL13
ID: 40395550
WOW!
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40396295
All you need is CDec() and one line:

    If Me.NewRecord Then
        Me.txtPOINVInvoiceID = CStr(CDec(DMax("[PO INV Invoice ID]", "PO Invoice") + 1)
    End If

/gustav
0
 

Author Closing Comment

by:SteveL13
ID: 40396882
That worked!  Thanks.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40396885
You are welcome!

/gustav
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question