using Dlookup to test for null value in field

[WayneRyan]

S,

If the customer has already processed the book, DLookUp will return the
Book_ID (we don't really care, but it returns something).

It will return a Null value if the customer has no activity.

You can't compare the DLookUp to a VBA string:

If DLookUp(...) = SomeVBAString ...

You will get "Invalid use of Null", if there's no activity.

So, if you want to check for activity (The DLookUp is Not Null):

If Not IsNull(DLookUp(...)) Then

That's the "outside" of it.

Inside the DLookUp:

DLookUp("[Field]", "Table", "Criteria")

Criteria = "[String] = 'SomeString' And [Number] = 123 And Date = #1/1/2004# And [SomeField] Is Not Null"

Wayne

 

[DB7]

Good to hear, smercer, & thanks for the appreciation. Believe it or not, though finally correct, I may have procrastinated the resolution. I think this one goes to Wayne.

 

[WayneRyan]

S & DB,

Glad to hear its OK.

I think we've beat this one to death. See you two later.

Wayne

 

[smercer]

Good to hear, smercer, & thanks for the appreciation. Believe it or not, though finally correct, I may have procrastinated the resolution. I think this one goes to Wayne.

I try to encourage success (and to be positive) with my signature, sadly It does not always work.

 

[EMP]

The following code you gave me works:

If DLookup("[Book_ID]", "tbl_Inventory_Each_Book", _
"([Book_ID]= '" & Me![Condenced_Book_ID] & "' ) and " & _
"([Customer_No]= " & Forms!frm_Book_Sales![txt_Cust_ID] & ") and " & _
"(Not [Date_Sold] Is Null)") = Me!Condenced_Book_ID Then

Since you already have [Book_ID]=Me!Condenced_Book_ID inside DLookup,
DCount can also do the trick:-

If DCount("*", "tbl_Inventory_Each_Book", _
     "[Book_ID]= '" & Me![Condenced_Book_ID] & _
     "' and [Customer_No]= " & Forms!frm_Book_Sales![txt_Cust_ID] & _
     " and [Date_Sold] Is not Null") > 0 Then

[Date_Sold] Is Not Null and Not IsNull([Date_Sold]) both work.