標題: ACCESS VBA 求指教 [打印本頁]

---

作者: Ponya    時間: 2023-7-11 15:08     標題: ACCESS VBA 求指教

本帖最後由 Ponya 於 2023-7-11 15:10 編輯

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

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    時間: 2023-7-11 16:23

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    時間: 2023-7-11 16:59

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

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

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

寫返if then else吧。

---

作者: Ponya    時間: 2023-7-11 17:14

謝謝樓上兩位師兄

---

作者: s20012797    時間: 2023-7-11 19:37

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    時間: 2023-7-11 23:13

本帖最後由 iamstupid 於 2023-7-11 23:15 編輯

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() vba - Google 搜索 - https://www.google.com/search?q= ... client=gws-wiz-serp

---

作者: s20012797    時間: 2023-7-11 23:26



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