Using fSort procedure to aqvoid too many nested IIF conditions in a query

[Steve1]

I've created 2 fSort modules in a query to pull 2 different fields from a table and assign a unique name to each. Both modules look identical syntax-wise.

This one works perfectly:
Option Compare Database
Option Explicit
Public Function fSort1(district As String)
Select Case district
Case "NC", "SC"
fSort1 = "NCSC"
Case "TN/KY - E", "TN/KY - W"
fSort1 = "TNKY"
Case "FL - N", "FL - S"
fSort1 = "FL"
Case "AL", "MS", "LA"
fSort1 = "ALMSLA"
Case "GA - Out", "GA - Atl"
fSort1 = "GA"
End Select
End Function


This one stops after the first record:

Option Compare Database
Option Explicit
Public Function fSort(ospcm As String)
Select Case ospcm
Case "NC EAST", "NC WEST"
fSort = "NC"
Case "SOUTH CAROLINA"
fSort = "SC"


Case "Georgia Atlanta"
fSort = "GA - Atl"
Case "GEORGIA OUTSTATE"
fSort = "GA - Out"
Case "ALABAMA FL', FL NOrth"
fSort = "FL - N"
Case "FL South"
fSort = "FL - S"
Case "ALABAMA"
fSort = "AL"
Case "MISSISSIPPI"
fSort = "MS"
Case "LOUISIANA"
fSort = "LA"
Case "kentucky e", "tn east"
fSort = "TN/KY - E"
Case "kentucky w", "tn west"
fSort = "TN/KY - W"
End Select
End Function



Thanks in advance,
Steve1

[orange]

Steve,

Is there a question here somewhere? Where would we use these?
Welcome to the forum. Good to see the use of Option Explicit.

[Steve1]

The question is why is the second only returning the first record??

[orange]

Did you test your code? Did you try calling your "function" from a loop situation?

Shouldn't one call to a function only return 1 value?
Please show us the code where you ran the function and it only returned the first record.

[Steve1]

It's the second set of code in my thread.

[June7]

Where is fSort function called from - query, textbox, VBA? How is the ospcm variable set? What is its value when the procedure fails? Does every record have a value in the relevant field?

[Steve1]

It's called from a procedure in a module. I figured it out though. The code was fine - the problem was that the field I was importing from Excel had embedded spaces in the data. Thanks.

[orange]

Glad you have it resolved.