Please help me with sorting two field out in microsoft access.

I have a field called reference number, format is L.L.000.00 (two letters, 3 num, 2 num)

i would like to enter the first two letters and last two numbers manually, however i want the middle set of numbers to increment by one each time.

The second field is similar. called Case No: format: 00.000 (2 num, 3 num)
the second set of numbers should increment by 1 each time.

By the way this data is going to be entered on a form, i have set the format and created all forms and tables, just cant get the increment thing to work.

thank you

You should have separate fields for each component of this ID. One of those fields should be a long integer field (call it Increment). On your form have a control bound to Increment with a default value of:

=Nz(Dmax("[Increment]","table"),0) + 1

To display the ID concatenate the compnents in an unbound control.

The criteria is more complicated than that. Plus you actually need to store the calculated value so you need to put code in the Form's BeforeInsert Event. This will assign the sequence number as soon as someone types a single character into the form. You have three criteria fields. All four pieces should be stored separately. They can be concatenated as necessary.

Me.MiddleThreeNumbers = Nz(Dmax("[MiddleThreeNumbers]","YourTable"),"FirstLeter = '" & Me.FirstLetter & "' AND SecondLetter = '" & Me.SecondLetter & "' AND LastNumber = " & Me.LastNumber) + 1

You should be aware of the possiblility of this technique generating a duplicate number when used in a multi-user environment and take appropriate action.

Hi Pat,
I didn't read into it that he wanted the increment to based on the other components. If he does, then your additions are correct.

You may be correct Scott, but the vast majority of time when people want to make one of these composite "keys" they want the serial number to be relative to the other components of the "key".

If the serial number could increment indefinitely, it would be far better to just use an autonumber.

Probably True.