MSAccess用戶函式運行但不顯示值

MS Access 新手在這里。我撰寫了一個 VBA UDF 來通過檢查級別（我的代碼中的 IL 變數）和批次大小（我的代碼中的批量變數）計算 AQL 樣本大小

在訪問用戶表單中，我的兩個變數的兩個值顯示在文本框中。然后我有第三個文本框，其中包含我的 UDF，當我添加或更改兩個變數中的任何一個時，我想自動更新到新值，但它永遠不會改變。它只是保持為零。

當我更新任一變數時，如果我在 UDF VBA 代碼中停止，代碼確實會運行，并在我逐步完成時在我的 VBE 中的 Watch 中為我提供正確答案。所以代碼似乎沒問題，我假設。只是我在嘗試回傳值的文本框屬性中做錯了，我猜。

如果有幫助，代碼如下。

另外，有點相關的問題，我猜你不能在表中使用 UDF？我可以在 excel 表中使用相同的 UDF，所以我想我應該可以，但似乎我不能。

謝謝

 Public Function SampleSize(IL As String, Batch As Long) As Long
If IL = "S1" Then
    Select Case Batch
    Case 2 To 50
        Sample = 2
    Case 51 To 500
        Sample = 3
    Case 501 To 35000
        Sample = 5
    Case Is >= 35001
        Sample = 8
    End Select
End If

If IL = "S2" Then
Select Case Batch
    Case 2 To 25
        Sample = 2
    Case 26 To 150
        Sample = 3
    Case 151 To 1200
        Sample = 5
    Case 1201 To 35000
        Sample = 8
    Case Is >= 35001
        Sample = 13
End Select
End If

If IL = "S3" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 50
        Sample = 3
    Case 51 To 150
        Sample = 5
    Case 151 To 500
        Sample = 8
    Case 501 To 3200
        Sample = 13
    Case 3201 To 35000
        Sample = 20
    Case 35001 To 500000
        Sample = 32
    Case Is >= 500001
        Sample = 50
End Select
End If

If IL = "S4" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 25
        Sample = 3
    Case 26 To 90
        Sample = 5
    Case 91 To 150
        Sample = 8
    Case 151 To 500
        Sample = 13
    Case 501 To 1200
        Sample = 20
    Case 1201 To 10000
        Sample = 32
    Case 10001 To 35000
        Sample = 50
    Case 35001 To 500000
        Sample = 80
    Case Is >= 500001
        Sample = 125
End Select
End If

If IL = "G1" Then
Select Case Batch
    Case 2 To 15
        Sample = 2
    Case 16 To 25
        Sample = 3
    Case 26 To 90
        Sample = 5
    Case 91 To 150
        Sample = 8
    Case 151 To 280
        Sample = 13
    Case 281 To 500
        Sample = 20
    Case 501 To 1200
        Sample = 32
    Case 1201 To 3200
        Sample = 50
    Case 3201 To 10000
        Sample = 80
    Case 10001 To 35000
        Sample = 125
    Case 35001 To 150000
        Sample = 200
    Case 150001 To 500000
        Sample = 315
    Case Is >= 500001
        Sample = 500
End Select
End If

If IL = "G2" Then
Select Case Batch
    Case 2 To 8
        Sample = 2
    Case 9 To 15
        Sample = 3
    Case 16 To 25
        Sample = 5
    Case 26 To 50
        Sample = 8
    Case 51 To 90
        Sample = 13
    Case 91 To 150
        Sample = 20
    Case 151 To 280
        Sample = 32
    Case 281 To 500
        Sample = 50
    Case 501 To 1200
        Sample = 80
    Case 1201 To 3200
        Sample = 125
    Case 3201 To 10000
        Sample = 200
    Case 10001 To 35000
        Sample = 315
    Case 35001 To 150000
        Sample = 500
    Case 150001 To 500000
        Sample = 800
    Case Is >= 500001
        Sample = 1250
End Select
End If

If IL = "G3" Then
Select Case Batch
    Case 2 To 8
        Sample = 3
    Case 9 To 15
        Sample = 5
    Case 16 To 25
        Sample = 8
    Case 26 To 50
        Sample = 13
    Case 51 To 90
        Sample = 20
    Case 91 To 150
        Sample = 32
    Case 151 To 280
        Sample = 50
    Case 281 To 500
        Sample = 80
    Case 501 To 1200
        Sample = 125
    Case 1201 To 3200
        Sample = 200
    Case 3201 To 10000
        Sample = 315
    Case 10001 To 35000
        Sample = 500
    Case 35001 To 150000
        Sample = 800
    Case 150001 To 500000
        Sample = 1250
    Case Is >= 500001
        Sample = 2000
End Select
End If
End Function

uj5u.com熱心網友回復：

您永遠不會為函式本身分配回傳值！

您應該按照指示放置這行代碼：

    SampleSize = Sample
End Function

此外，在函式范圍內宣告變數也是一種好習慣。

Dim Sample As Long

宣告后，它將被初始化為 0，這也將是您的默認回傳值，并且與您的函式型別相同。

未宣告的變數將是 Variant 型別并初始化為 Empty...

標籤：vba毫秒访问用户定义函数

上一篇：附加帶有特殊字符的字串時，MSAccessVBASQLAppend查詢回傳語法錯誤

下一篇：根據其他欄位的值求和欄位