Thread: Public Function w/ Case Statement

I have a table with a field called 'OLanguage'. This is a text field and stores a 2 character language (i.e. WO). I would like to use the OLanguage field to populate a field in a query called 'OTeam'.

To accomplish this task I would like to use a VBA public function and insert several case statements. I have called the function in the query as 'OTeam: get_team([Team])

As far as the VBA coding... this is what I have so far, but it does not work.

Public Function get_team(OLanguage As String)
  
       Select Case OLanguage Is Not Null
      
        Case "WO"
            get_team = "A"
            
        Case Else
            get_team = "Other Team"
                        
    End Select
End Function

Thx in advance

What is not happening or what is happening. Tough to diagnose without details. As it is, it is an excercise in 'fishing'. Perhaps some more details. Describe your query in more detail, even supply your SQL statement so that we can see the whole picture. Are there only two cases or do you have many? If only two, an IIF statement would work, but I suspect you have many.
Is this a one time event or will it be ongoing? If a one time event, then perhaps an update query?

Alan

Thx for replying Alan

I've attached a sample mdb file for reference. I use Access 2002 in my organization.
(don't ask me why we haven't upgraded)

Query and SQL syntax is included of course. In regards to the case statements... I will be using many (the VBA code is in Module1).

The query will be run every month.

Look at this thread. It appears to be what you are trying to do.

http://www.access-programmers.co.uk/...d.php?t=183887

Alan