Autogenerarte a number but with a difference

[wullieb1]

Autogenerate a number but with a difference

Folks,

Newbie to this forum so please be gentle. I am a relative newbie to acces but i seem to be picking it up quite quickly.

My problem is this:

I have an enquiry form made up and a button on the form that will generate a number by using the following code:

Private Sub EnqGen_Click()
On Error GoTo Err_EnqGen_Click

    Me![Enquiry Number] = NewEnqNum()
    Me![Customer Name].SetFocus
    Me![Enquiry Number].Enabled = False
    
Exit_EnqGen_Click:
    Exit Sub
    
Err_EnqGen_Click:
    
    MsgBox "Error " & Err & ": " & Error$
    Resume Exit_EnqGen_Click
    
Exit Sub

End Sub

Public Function NewEnqNum() As Long
On Error GoTo NextEnq_Err

    Dim lngNextEnq As Long
    
    lngNextEnq = DMax("[Enquiry Number]", "Enquiry") + 1
    
    NewEnqNum = lngNextEnq
    
Exit_NewEnqNum:
Exit Function

NextEnq_Err:
    
    MsgBox "Error " & Err & ": " & Error$
    Resume Exit_NewEnqNum
    
Exit Function

    
End Function

This code works perfectly and generates a new enquiry number for me.

The end user now wants to change the format of the number to read like this 06-0001, whereas before i was using 60001.

What i would like to know is there anyway i can increment the last 4 digits only? i.e remove the first 3 digits, 06-, increment the 0001 by 1 then reassemble them back together.

Thanks for your help.

 

[FoFa]

You could keep them in seperate fields.
But I would keep it like it is and handle the display format when you display the number.
Look up numeric format function options in help. It will probably be something like: FORMAT([YourNumber],00-0000) but read help in your version to get it correct

 

[edtab]

You can extract portions of a string using the "left" and "right" function.
For explanation and example of these two functions, look in the Help section of your Access menu bar.

 

[wullieb1]

Excellent pointer folk's.

I'll start working on it right away.

Pat,

The user will require a new database each year, i know don't ask, so i can set the year manually.