Request # according to dept

[kastaman]

Hi there,

I hope somebody has come accross the following situation:

I'm creating a simple request database where there are 4 different departments using this db.

The requirement is for the db to have a sequenced numbering but with a different starting number according to the department. Here's an example:

Dept. Starting #
A 30001
B 50001
C 70001
D 90001

So each a department sends a request, the db will create the next available number in the form like for department A:
30002 then 30003.

Can this be accomplished in one table with some special coding on the request # field?

Thanks in advance,

Kastaman

 

[KenReay]

Hi

Something like:

Private Function NextNumber(Dept As String) As String
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim strSQL As String
'
Set Db = CurrentDb()
strSQL = "SELECT RequestId FROM tblRequest WHERE RequestId Like '" & Dept & "' ORDER BY RequestId DESC;"
Set Rs = Db.OpenRecordset(strSQL)
If Rs.RecordCount < 1 Then
NextNumber = Format(Asc(Dept)- 62,&quot;00&quot & &quot;00001&quot;
Else
NextNumber = Format(Asc(Dept)- 62,&quot;00&quot & Format(Val(Mid(Rs!RequestId,2)) + 1, &quot;00000&quot
End If
Rs.Close
Set Rs = Nothing
Set Db = Nothing
End Function

Please note

Above not tested

I have assumed two digin prefix since there are 26 letetrs in the alphabet, but if you know less that 10 are in use (ie A-J) then you can modify Format() accordingly

You will need error trapping, particularly in a multi user environment

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK