ACCESS VBA 求指教

Ponya

想請問各位師兄, 師姐, 下面呢一句有甚麼問題?

Chk_Flag = IIf(IsNull(race), True, IIf(Val(race) < 0 Or Val(race) > 3, True, False))

RACE 在TABLE入面定義為STRING, 一個位寬.

一RUN時就出現下面ERROR MESSAGE "執行階段錯誤'94', Null 的使用不正確"

我嘗試分開獨立RUN, 第一句 "ISNULL(RACE)" 可正常出現TRUE, 但後面嗰句獨立RUN, 又會出現番上面個ERROR MESSAGE.

但我又吾多明白, 在無拆散下RUN, 明明個 ISNULL(RACE) 以出咗個TRUE 結果, 但好像個PROGRAM仍然去做TEST後面嗰部份? 所以出現相同既ERROR MESSAGE.

javacomhk

The IIF function executes both the TRUE and the FALSE sections of the line of code – it does not exit the code once it has found the condition to be true – it still executes the false section as well – thus giving us a false message in the second message box.  If you had used  If..Then..Else..End If – this would not have occurred – the IF function only executes either the TRUE or the FALSE section of the code – depending on the logic being passed to the code.

iamstupid

N年無寫access vba....  2樓說的可能對的。

iif()原意不是for vba，而是for query用。

我自己寫vba唔會用哩類偷懶function，同在excel寫一大抽nested IF無分別，難debug。

寫返if then else吧。

Ponya

謝謝樓上兩位師兄

s20012797

The error message "Execution phase error '94', incorrect use of null" appears because the IsNull function is not used correctly. The IsNull function returns True if the expression is Null; otherwise, it returns False. In your code, the first IIf statement checks if the value of race is Null. If it is Null, then the value of Chk_Flag is set to True. If it is not Null, then the second IIf statement checks if the value of race is less than 0 or greater than 3. If it is less than 0 or greater than 3, then the value of Chk_Flag is set to True. Otherwise, the value of Chk_Flag is set to False.

The problem is that you are trying to use the Val() function on a string. Val() can only be used on numbers, so when you try to use it on a string, you get an error.

To fix this, you need to convert the string to a number before you use the Val() function. You can do this using the CStr() function. For example, you could use the following code:

Chk_Flag = IIf(IsNull(race), True, IIf(CStr(race) < 0 Or CStr(race) > 3, True, False))

The CStr() function converts a string to a number. So, in the example code, the CStr() function will convert the value of the race variable to a number. Then, the Val() function will be used to check if the number is less than 0 or greater than 3. If it is, then the Chk_Flag variable will be set to True. Otherwise, it will be set to False.

Or.

Here's how you can modify your code:

Chk_Flag = IIf(IsNull(race), True, IIf(Nz(race) < 0 Or Nz(race) > 3, True, False))

The error message appears because you are trying to use Val() function on a Null value. You can use Nz() function instead of Val() function to handle Null values. Nz() function returns a specified value if a Variant is Null; otherwise, it returns the value of the Variant.

IsNull function (Visual Basic for Applications) | Microsoft Learn. https://learn.microsoft.com/en-u ... lp/isnull-function.

VBA IsNull Function - Automate Excel. https://www.automateexcel.com/vba/isnull-function/.

iamstupid

5樓ching寫咁大段野本來我都興趣睇，但見到竟然話“The CStr() function converts a string to a number. So, in the example code, the CStr() function will convert the value of the race variable to a number.“！？

曾寫過十幾年access vba的我真係睇到眼凸！！
無力吐槽 ，自己睇吧：


Type conversion functions (VBA) | Microsoft Learn - https://learn.microsoft.com/en-u ... onversion-functions

CStr function example

This example uses the CStr function to convert a numeric value to a String.
VB
Dim MyDouble, MyString
MyDouble = 437.324 ' MyDouble is a Double.
MyString = CStr(MyDouble) ' MyString contains "437.324".

CStr() vba - Google 搜索 - https://www.google.com/search?q= ... client=gws-wiz-serp

s20012797

5樓ching寫咁大段野本來我都興趣睇，但見到竟然話“The CStr() function converts a string to a number. S ...
iamstupid 發表於 2023/7/11 23:13

好日都吾會寫下ED野鬼Q記得咁詳細咩,不過謝指教+1