Grouping from Field Data 2

[Stangleboy]

I have been out of the VBA scene for a long time and this seems easy but I have been searching the FAQ and other posts since 8:00 am and have not found code that can help me. Any help would be appreciated.
I want to populate a field in a table from the data entered into another field in the same table. I need to assign a value to an employee based on the last digit (5 characters) of their employee number. The table that the data is assigned to is dbo.Employee and the field is “EmpNumber” and the value needs to populate the same table but the field is “EmpGroup”. Would I use an IF function or DLookup:
Any employee number ending with 1, 2 or 3 should be assigned a “1” in EmpGroup field.
Any employee number ending with 4, 5 or 6 should be assigned a “2” in EmpGroup field.
Any employee number ending with 7, 8, 9 or 0 should be assigned a “3” in EmpGroup field.
If you know a FAQ or post that you can refer me to that looks at the last character in the EmpNumber field and then groups the character to the right number that would be great. Thank you in advance for your assistance.

 

[traingamer]

This being access, you could do it with a query.

Update Employee
set empGroup =
iif(right(EmpNumber, 1)>6, "3", iif(right(EmpNumber, 1)>3, "2", "1"))

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MajP]

There are several ways you could do this. One would be to build a function in a standard module.

Public Function getEmpGroup(empNumber As Variant) As Integer
  If Not IsNull(empNumber) Then
     empNumber = CStr(empNumber)
     getEmpGroup = ((Val(Right(empNumber, 1)) - 1) \ 3) + 1
  End If
End Function

then in a sql

Select
empNumber,
getEmpGroup([empNumber]),
other fields
from
sometable

The above function would allow you to continue adding groups of three.

 

[traingamer]

And, of course, my original one wouldn't work correctly for the numbers ending in zero.

Update Employee
set empGroup =
iif(right(EmpNumber, 1)>6 or right(EmpNumber, 1)=0, "3", iif(right(EmpNumber, 1)>3, "2", "1"))

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Stangleboy]

Thank you for your fast replies:

Traingamer- when I add the code into a query it places quotes around the field name and I get an error, I switched it to brackets around the field name and get no results. Also where would a '0' fit in group 3?

Also every time a new employee gets added do I need to run the query, which I can do with a macro?

MajP- I did this but it didn not populate the field.

Again thank you both.

 

[Stangleboy]

Oops, missed the cross thread traingamer; I used to use Select Case when I needed to group but it is not working. What am I missing here?

Private Sub EmpNumber_LostFocus()

Select Case EmpNumber
Case Right((EmpNumber), 1) = 0
EmpGroup = "3"
Case Right((EmpNumber), 1) = 7 To 9
EmpGroup = "3"
Case Right((EmpNumber), 1) = 4 To 6
EmpGroup = "2"
Case Right((EmpNumber), 1) = 1 To 3
EmpGroup = "1"
End Select
End Sub

Thank you.

 

[MajP]

This Right((EmpNumber),1) returns a string not a number

val(right(EmpNumber,1))

 

[Stangleboy]

I decided to go with an If statement, thank you both for your time and help, I couldn't get the last character of the employee right without your feedback and answers!!!

Code Used:
Private Sub EmpNumber_LostFocus()

If Right((EmpNumber), 1) = 0 Then
EmpGroup = "3"
ElseIf Right((EmpNumber), 1) > 6 Then
EmpGroup = "3"
ElseIf Right((EmpNumber), 1) > 3 Then
EmpGroup = "2"
Else
EmpGroup = "1"
End If

End Sub

 

[PHV]

As for the Select Case:
Select Case Right((EmpNumber), 1)
Case 0, 7 To 9
EmpGroup = "3"
Case 4 To 6
EmpGroup = "2"
Case 1 To 3
EmpGroup = "1"
End Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886