Create MS Access VBA Function for Multiple Conditions

[nguyenak]

Hi,



Im trying to create the following function in VBA for MS Access 2010.

columnA columnB columnC
A D French
B E German
C F

Something like

Function translateColumn(ColumnA as string, ColumnB as String)
If Column A = "A" and ColumnB = "D" then translateColumn = "French"
else if
If Column A = "B" and ColumnB = "E" then translateColumn = "German"
else if
If Column A = "C" and ColumnB = "F" then translateColumn = "English"


Can this be accomplised through VBA...

Any help would is appreciated...

thanks!

[pbaldy]

Sure, but it would make more sense to have the values in a table and simply look up the language. Much more flexible than having it hard coded in a function. You could do it the way you have it, but I'd probably use nested Select/Case, like this pseudo-code:

Select Case ColumnA
  Case "A"
    Select Case ColumnB
       Case "D"
          translateColumn = "French"
       Case "E"
...
  Case "B"
...

But I'd have it all in a table.

[rzw0wr]

Function translateColumn(ColumnA as string, ColumnB as String)
If Column A = "A" and ColumnB = "D"  then translateColumn = "French"
else if
If Column A = "B" and ColumnB = "E"  then translateColumn = "German"
else if
If Column A = "C" and ColumnB = "F"  then translateColumn = "English"

If ColumnA = "A" AND ColumnB = "D" then
      translateColumn = "French"
ElseIf ColumnA = "B" AND CoulmnB = "E" then
      translateColumn = "German"
ElseIf ColumnA = "C" AND ColumnB = "F" Then
      translateColumn = "English"
End If

EDIT: Paul's post is better an easier to troubleshoot.

Dale

[nguyenak]

I've attached the associated Access File.

I tried to add the function as a module. Afterwards, I use expression builder to create the Language Column.
However, I can't get it to work.

Im new at this and really need help.

[pbaldy]

Just add it to the query (which I assume is where you want to use it):

SELECT Sheet1.Type, Sheet1.Page, translateColumn([type],[page]) AS [Language]
FROM Sheet1;

[nguyenak]

I added Paul's Module:

Function translateColumn(Domain As String, Page As String) As String
    Select Case Domain
              Case "A"
                     Select Case Page
                               Case "D"
                                     translateColumn = "English"
              Case "B"
                     Select Case Page
                               Case "E"
                                      translateColumn = "French"
            
            End Select
End Select
End Select
End Function 

But it stopped executing after the first row. Im not sure what im doing

[pbaldy]

Does it help if I correct the indenting?

  Select Case Domain
    Case "A"
      Select Case Page
        Case "D"
          translateColumn = "English"
        Case "B"
          Select Case Page
            Case "E"
              translateColumn = "French"

          End Select
      End Select
  End Select

[nguyenak]

Results are shown above

[pbaldy]

So the corrected indenting didn't help? I don't think you understand the structure of Select/Case. Your test of "B" occurs as a test of the Page variable, not domain. You want this type of thing with Select/Case:

Function translateColumn1(Domain As String, Page As String) As String
  Select Case Domain
    Case "A"
      Select Case Page
        Case "D"
          translateColumn1 = "French"
        Case "E"
          translateColumn1 = "German"
        Case "F"
          translateColumn1 = "English"
      End Select
    Case "B"
      Select Case Page
        Case "E"
          translateColumn1 = "French"
      End Select
    Case "C"
      Select Case Page
        Case "F"
          translateColumn1 = "English"
      End Select
  End Select
End Function

[nguyenak]

Works perfect now...you're the man Paul!

[pbaldy]

Happy to help! But I'd still have the values in a table.

[nguyenak]

Last question, for the Select Case, are we able to use wildcards?

For example

Instead of Case "A" , it would be Case like "*A*"

[pbaldy]

I don't believe you can use Like in a Case statement. You could use the Left() function if you wanted to test the first character.

[nguyenak]

then nested "if then" statement would be appropriate?

[pbaldy]

Sure, if you want to keep it hard coded.