VBA data type error - bad syntax?

Question

Hopefully someone can help me out with this. I have written a query in Access 2003 that combines a linked table "taxon_group_max_per_site" and a cross tab query "Distinct Species by group_Crosstab".

From the table I have the field "Taxonomic Group" and "Max", and from the cross tab the fields "Total_Of_Species_S".

The table and the cross tab are linked and the query works fine until I add in some VBA to give each Taxonomic group a score based on "Max" and "Total_Of_Species_S".

The code below brings up "Error 13: type mismatch"

Public Function Invert_Diversity_Score (Total_Of_Species_S As Integer) As Integer

    If Total_Of_Species_S < Round("[Max]*0.5", 0) Then
      Invert_Diversity_Score = 0
    Else
      If Total_Of_Species_S < Round("[Max] * 0.75", 0) Then
        Invert_Diversity_Score = 1
      Else
        If Total_Of_Species_S < Round("[Max] * 0.875", 0) Then
          Invert_Diversity_Score = 2
        Else
          Invert_Diversity_Score = 3
        End If
      End If
    End If

End Function

The debugger shows that "[Max]*0.5" and the other multiplications do not produce a number it says "[Max] * 0.5"= "[Max] * 0.5", which I think is the source of the type mismatch. How do I get the field to multiple properly? It looks exactly like the format shown in the VBA help.

Answer 1

The round function is expecting a number as parameter, not a string! Assuming (max) is a number, you can then calculate:

Round([Max] * 0.75, 0)

But

Round("[Max] * 0.75", 0)

Will definitely not return anything viable

Answer 2

For one, you should use the ElseIf keyword, there is no need to stack the Ifs here.

Second - what is "[Max]*0.5" supposed to mean? To VB, it is a string, which unsurprisingly you can't multiply with an integer.

Assuming Max is some kind of global constant:

Public Function Invert_Diversity_Score(Total_Of_Species_S As Integer) As Integer

  If Total_Of_Species_S < Round(Max * 0.5, 0) Then
    Invert_Diversity_Score = 0
  ElseIf Total_Of_Species_S < Round(Max * 0.75, 0) Then
    Invert_Diversity_Score = 1
  ElseIf Total_Of_Species_S < Round(Max * 0.875, 0) Then
    Invert_Diversity_Score = 2
  Else
    Invert_Diversity_Score = 3
  End If

End Function

If it is not a constant, then you must pass it into the function as well:

Public Function Invert_Diversity_Score( _
  Total_Of_Species_S As Integer, _
  Max as Integer _
) As Integer

Answer 3

"[Max] * 0.875" is just a string, how is VBA supposed to know that you are referring to the column [Max] from one of your tables?

Shouldn't [Max] be passed into the function as a second integer parameter? Something like this:

Public Function Invert_Diversity_Score (Total_Of_Species_S As Integer, 
                        MaxVal as Integer) As Integer

We need the code that shows how you are calling the function to really sort this out ...