Function to assign order values

[Les Isaacs]

Hi All

I have a table [staffs] with a string fields [staff_name] and [practice] and another field [bank ref order] (integer). I need a function that will assign values to [bank ref order] according to thre alphabetical order of [staff_name] - so
[staff_name] of Andrew Smith gets a [bank ref order] value of 10
[staff_name] of Brian Jones gets a [bank ref order] value of 20
[staff_name] of Charles Brown gets a [bank ref order] value of 30
etc.

So far I have:

Dim rsEmployees As DAO.Recordset
Dim ThisEmployee as String
Dim order as Integer
 
order = 10
 
Set rsEmployees = CurrentDb.OpenRecordset("SELECT staffs.practice, staffs.staff_name, staffs.[bank ref order]" & _
                  " from staffs" & _
                  " WHERE staffs.practice= """ & practicename & """ " & _
                  " ORDER BY staffs.staff_name;")
 
        rsEmployees.MoveFirst
        While Not rsEmployees.EOF
ThisEmployee = rsEmployees![staff_name]
With ThisEmployee
rsEmployees![bank ref order] = order
order = order + 10
Next

... which I think is on the right lines, but obviously my syntax is out!

Hope someone can help.
Many thanks in advance
Les

 

[bob fitz]

Does the field [bank ref order] already hold a value?
What is the purpose of this field?

 

[Les Isaacs]

Hi Bob

[bank ref order] holds the sequence number that I now need to assign! There are some legacy values in there for some records, but these need to be updated.

Hope that answers your question, and thanks for any help.
Les

 

[bob fitz]

I don't understand the need for a "sequence" number?

 

[Les Isaacs]

Hello Bob
I appreciate your intention to help, but am not sure why you need to know the underlying reasons for the requirement that we have!
But since you ask, it's so that we can present certain reports sorted by employee according to the order requested by the client: some clients want first name order, some want surname order, some want DOB order, some want employment start date order, ... ! So once I get a function that will assign sequence numbers according to one field ([staff_name]) I can amend it to use whatever field is needed for a particular client.
Hope that makes sense!
Thanks again
Les

 

[JHB]

If practicename is a variable, then the line is wrong:

" WHERE staffs.practice= """ & practicename & """ " & _

Either so

" WHERE staffs.practice= " & "'" & practicename & "'" & " " & _

or so

" WHERE staffs.practice= '" & practicename & "' " &

 

[Les Isaacs]

Hi JHB

Thanks for that - I've made the change, and a couple of other changes - and now have it working!

Thanks again
Les

 

[bob fitz]

Hello Bob
I appreciate your intention to help, but am not sure why you need to know the underlying reasons for the requirement that we have!
But since you ask, it's so that we can present certain reports sorted by employee according to the order requested by the client: some clients want first name order, some want surname order, some want DOB order, some want employment start date order, ... ! So once I get a function that will assign sequence numbers according to one field ([staff_name]) I can amend it to use whatever field is needed for a particular client.
Hope that makes sense!
Thanks again
Les

Hi Les
I'm glad you have it working as you require

I was asking the question because I had not considered using the solution that you have used. I would have looked at constructing a "Sort By" clause for the recordset's SQL statement.