asked on

Another "autonumber" issue with a text field

An expert just helped by with a situation where I am trying to autonumber a text field.

The new situation is I have a text field that looks like this: SP14-00170. The next new record should be SP14-00171.

The solution in the previous topic was to create a query with one field that looked like:

LastTaskCardN: Max(Right([SAItaskCardN],InStr([SAItaskCardN],"-")+1))

And then use this code in the before insert event of the form:

    If Me.NewRecord = True Then
    Dim NewNum As String
        NewNum = DMax("LastTaskCardN", "qryLastSAItaskCardN")
        Me.txtSAItaskCardN = "TC-" & Format(CInt(NewNum) + 1, "0000")
    End If

Open in new window

But now my new situation is different (SP14-00170) instead of AB-1023 in the other topic.

How can I accomplish the same thing which SP14-00170 instead of AB-1023 ??

Scott McDaniel (EE MVE )

It's the same process, except you use a 5 digit format code, and preface the number with "SP14" instead of "AB". Did you try to adapt that logic to your current problem before posting here???

Unless you have other requirements which you haven't spelled out here.

SteveL13

ASKER

Yes I did. But I can't get the query to work. With this I get an error "Data type mismatch in query expression". (SAILotN is a text field just as SAItaskCardN was).

LastTaskCardN: Max(Right([SAILotN],InStr([SAILotN],"-")+1))

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

The minus 1 gave me the same error.

LastTaskCardN: InStr([SAILotN],"-") gave me a result of 5

Then in experimenting this gave me the f digits I was expecting:

LastTaskCardN: Right([SAILotN],InStr([SAILotN],"-"))

Then this code worked:

    If Me.NewRecord = True Then
    Dim NewNum2 As String
        NewNum2 = DMax("LastSAIlotN", "qryLastSAIlotNumber")
        Me.txtSAIlotN = "SP14-" & Format(CInt(NewNum2) + 1, "00000")
    End If

Open in new window

Another &quot;autonumber&quot; issue with a text field

Another "autonumber" issue with a text field