Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# DMAX with Letters - Alphanumeric

Posted on 2006-07-13
Medium Priority
495 Views
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:

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
Question by:martywal

LVL 4

Assisted Solution

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

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

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

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

ID: 17112344
0

## Featured Post

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month11 days, 16 hours left to enroll