Boolean Trouble with If statement VBA Access

Question

I am confused on the conditional operators of VBA. It probably has something to do with weird type casting, but I am stuck and need some help -

query = "SELECT * FROM [records_table] " & _
    "WHERE [po_number] = " & Chr(39) & po_number & Chr(39) & ";"

Set rec_set = data_base.OpenRecordset(query)
MsgBox rec_set.Fields("po_ack")
If rec_set.Fields("po_ack") <> Null Then _
    po_ack = True
If rec_set.Fields("po_ack") = Null Then _
    po_ack = False

Now, this is supposed to return true, but it returns false? When I msgbox the record set value, I get "11/12/2012", but when I msgbox the return of the function, it is false?

po_ack stores a Date value, and the value of this particular one is "11/12/2012", so why when I check it against null, it says that "11/12/2012" == null?

Thanks in advance for any help!

HansUp · Accepted Answer · 2012-11-12 19:13:12Z

3

Null is never equal to anything, not even another Null.

Null is never not equal to anything, not even another Null.

Use the IsNull() function to check whether something is Null.

If IsNull(rec_set!po_ack) = False Then
    po_ack = True
Else
    po_ack = False
End If

See if the IsDate() function could be useful for what you want to do. Here are some sample expressions copied from the Immediate window.

? IsDate(Null)
False
? IsDate(Now())
True
? IsDate("2012-11-12")
True
? IsDate("No way, dood")
False

edited Nov 12, 2012 at 19:13

answered Nov 12, 2012 at 18:51

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brad Larson · Accepted Answer · 2012-11-17 15:13:42Z

1

Sir I think you should take a look at the links listed below. There is a huge difference between NULL, Nothing and a blank value in VB. Null is something unknown, so it is not equal to 1, TRUE, "" or anything else. Never compare a variable with null because it results in incorrect logic. Check if you are confusing these types.

http://www.techrepublic.com/article/learn-the-differences-between-is-null-and-isnull-in-access/5034252

http://www.everythingaccess.com/tutorials.asp?ID=Common-Errors-with-Null

http://www.tek-tips.com/faqs.cfm?fid=3710

edited Nov 17, 2012 at 15:13

Brad Larson

170k45 gold badges401 silver badges574 bronze badges

answered Nov 12, 2012 at 18:53

Rikki

3,5681 gold badge25 silver badges34 bronze badges

Collectives™ on Stack Overflow

Boolean Trouble with If statement VBA Access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related