Return Max Numbers for a field 1

[Delano29]

Hi, I have a field that I use as a unique id/counter in a database. The field is numeric and the total length is 10 digits. The first 6 digits of the number are used to identify the departments in my company ex:

744445 (accounting department)
745587 (personnel department), etc.

The last four digits are added onto the above department numbers during record entry to increment the count of that particular departments record entry and this is done manually by the user ex:

7444450001, 744450002, etc.
7455870001, 7455870002, 745587003, etc.

I have about 8 departments in my company and don’t expect it to increase. What I would like to do in the user entry form either in fields (8 separate for each department) or listbox showing the highest number already saved in the database for that department. This will enable the user to check the list or field(s) to know which number they can use next for that department ex:

The highest number for accounting: 744445-0002
The highest number for personnel: 745587-0003

Is there a code I could put in a field(s) or listbox that will show the max for each department seperately?

Thanks.

 

[PHV]

A starting point as RowSource for a ListBox (SQL code):
SELECT Max([yourNumberField]) As Highest
FROM yourTable
GROUP BY Left([yourNumberField], 8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo.
SELECT Max([yourNumberField]) As Highest
FROM yourTable
GROUP BY Left([yourNumberField], [!]6[/!])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Delano29]

Thanks PHV for your prompt reply. The code works just great. I was wondering though what kind of changes I would need to make in the code to have just a single departments highest number appear in a single unbound field on the form so that I may manipulate the number individually as well, ex:

Accounting: 744445-0002

I'll change the code for each department and put them in individual unbound fields on my form.

By the way would this also work with a text field as well that contains only numbers?

Thanks again.

 

[PHV]

Have a look at the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886