Solved

Trying to increment a text "number" by 1

Posted on 2014-10-21
15
146 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40395217
or

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

then you need to revise this to accommodate  more characters..
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

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 119

Expert Comment

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now