VBA 数据类型错误 - 语法错误?

发布于 2024-08-04 16:06:41 字数 1059 浏览 2 评论 0原文

希望有人能帮助我解决这个问题。我在 Access 2003 中编写了一个查询,该查询结合了链接表“taxon_group_max_per_site” 和交叉表查询“Distinct Species by group_Crosstab”。

在表格中,我有字段“Taxonomic Group”“Max”,在交叉表中,有字段“Total_Of_Species_S”

表和交叉表已链接,查询工作正常,直到我添加一些 VBA 以根据 “Max”“Total_Of_Species_S” 为每个分类组提供分数。

下面的代码显示 “错误 13:类型不匹配”

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

调试器显示 “[Max]*0.5” 和其他乘法不会产生它所说的数字 "[Max] * 0.5"= "[Max] * 0.5",我认为这是类型不匹配的根源。如何正确地将字段设置为倍数?它看起来与 VBA 帮助中显示的格式完全相同。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

好多鱼好多余 2024-08-11 16:06:41

round 函数需要一个数字作为参数,而不是字符串!假设 (max) 是一个数字,你可以计算:

Round([Max] * 0.75, 0)

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

绝对不会返回任何可行的东西

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

身边 2024-08-11 16:06:41

“[Max] * 0.875”只是一个字符串,VBA 如何知道您正在引用表之一中的 [Max] 列?

[Max] 不应该作为第二个整数参数传递到函数中吗?像这样的事情:

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

我们需要显示如何调用函数的代码来真正解决这个问题......

"[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 ...

薔薇婲 2024-08-11 16:06:41

其一,您应该使用 ElseIf 关键字,无需在此处堆叠 If

其次 - "[Max]*0.5" 是什么意思?对于 VB 来说,它是一个字符串,毫不奇怪,它不能与整数相乘。

假设 Max 是某种全局常量:

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

如果它不是常量,那么您也必须将其传递到函数中:

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

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文