Solved

Trying to increment a text "number" by 1

Posted on 2014-10-21
15
147 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 35

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 35

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 49

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 49

Expert Comment

by:Gustav Brock
ID: 40396885
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 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