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
Solved

Trying to increment a text "number" by 1

Posted on 2014-10-21
15
148 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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