Case Select problem

[Guest]

This case is not working:
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I would expect 60134 to force rs![Owner] = 22. 60134 is producing the
normal incrementation as all of the other zips, i.e. 1 through 20 instead of
a static 22. This must be one of those cases of just looking at the problem
too long!

Thanks

 

[Marshall Barton]

This case is not working:
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I would expect 60134 to force rs![Owner] = 22. 60134 is producing the
normal incrementation as all of the other zips, i.e. 1 through 20 instead of
a static 22. This must be one of those cases of just looking at the problem
too long!

You need to use commas instead of OR in the Case statements.

Case "60505", "60564"

 

[Guest]

It worked, but I do not see the code that made the big difference. Would you
please point it out to me?

This case is not working:
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I would expect 60134 to force rs![Owner] = 22. 60134 is producing the
normal incrementation as all of the other zips, i.e. 1 through 20 instead of
a static 22. This must be one of those cases of just looking at the problem
too long!

You need to use commas instead of OR in the Case statements.

Case "60505", "60564"

 

[Marshall Barton]

The big difference is that the case statements now do what
you wanted them to do. With the OR, you were using Case to
select for True (the value of the expression).

Consider this:
Select Case X
Case 3,5
checks for X having a value of 3 or 5.

But
Select Case X
Case 3 Or 5
checks for X having a value of 7 (because the VBA OR
operator does a bitwise operation).
--
Marsh
MVP [MS Access]

It worked, but I do not see the code that made the big difference. Would you
please point it out to me?

This case is not working:
Public Sub AssignOwnerDF()
Dim Db As DAO.Database
Dim rs As DAO.Recordset

DAO.DBEngine.SetOption dbMaxLocksPerFile, 150000
Set Db = CurrentDb
RecordCount = DCount("*", "tblWholesaler")
Set rs = Db.OpenRecordset("tblWholesaler") '

Mgr = 0
For RecCount = 1 To RecordCount 'Traverse table
If Mgr = 20 Then
Mgr = 0
Else
Mgr = Mgr + 1
rs.Edit
Select Case rs![Zip]
Case "60505" Or "60564"
rs![Owner] = 21
Case "60134" Or "60151" Or "60174" Or "60510" Or "60542" Or
"60554"
rs![Owner] = 22
Case Else
rs![Owner] = Mgr
End Select
rs.Update
rs.MoveNext
End If
Next RecCount

End Sub

I would expect 60134 to force rs![Owner] = 22. 60134 is producing the
normal incrementation as all of the other zips, i.e. 1 through 20 instead of
a static 22. This must be one of those cases of just looking at the problem
too long!

You need to use commas instead of OR in the Case statements.

Case "60505", "60564"