Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DMAX with Letters - Alphanumeric

Posted on 2006-07-13
5
Medium Priority
?
461 Views
Last Modified: 2011-04-14
I have a form where I have a control that calculates a DMax for a field in a table. This works fine and the current value is AT1372

I now need to create the next ID number which will be fed into the next episode that is entered into the database (the answer should be AT1373). Unfortunately I cannot get it to add another digit.

The textbox that I have created which gives the DMax have the following equation:

=DMax("[Episode]","UploadCCT")
Textbox is called EPISODEmax

As I mentioned above this is working

I have tried NZ and also +1 outside brackets etc but it won't work. Is there a way of doing this or will the letters (AT) be preventing the calculation?
0
Comment
Question by:martywal
[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 Comments
 
LVL 4

Assisted Solution

by:Carl2002
Carl2002 earned 300 total points
ID: 17100564
marytwal

you are correct in your thinking that the AT is preventing the addition as its a textfield. you need to think about splitting the text and numbers adding 1 to the number and then concatenanting again
0
 
LVL 44

Accepted Solution

by:
GRayL earned 400 total points
ID: 17100599
Assuming there is always a two-letter prefix try this:

=left(mystr,2) & strreverse(val(strreverse(mystr)))+1
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 300 total points
ID: 17100608
U could perhaps use this function to extract the numeric, then add one
e.g.

Public Function ExtractNumeric(ByVal sWord As String) As String

    Dim i As Integer
    Dim sNewNumber As String
   
    sNewNumber = ""
    For i = 1 To Len(sWord)
        If IsNumeric(Mid$(sWord, i, 1)) Then sNewNumber = sNewNumber & Mid$(sWord, i, 1)
    Next i
    ExtractNumeric = sNewNumber
End Function


' test sub

Public Sub TestSub()

    Dim sOldValue As String
    Dim sNewValue As String
   
    sOldValue = "AT1327"
    sNewValue = ExtractNumeric(sOldValue)
    If sNewValue <> "" Then
        sNewValue = Replace(sOldValue, sNewValue, Val(sNewValue) + 1)
    Else
        sNewValue = 1
    End If
   
    MsgBox "From " & sOldValue & vbCrLf & "to " & sNewValue
End Sub

0
 

Author Comment

by:martywal
ID: 17100694
THanks Experts, I ended up going for the split out into two text boxes and concatenating which did the trick.

Will split points ;-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17112344
Thanks, glad I could help.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 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