using Dlookup to test for null value in field

[smercer]

Hi all,

I am trying to make this code work for finding whether the Date_Sold has a value in it but do not know how to incorperate a "Not isNull()" thing in there

heres my code:

If DLookup("[Book_ID]", "tbl_Inventory_Each_Book", "([Book_ID]= me![Condenced_Book_ID]) and ([Customer_No]=[txt_Cust_ID]) and (" & Not IsNull([Date_Sold]) & ")") = Me!Condenced_Book_ID Then

Where have I gone wrong?

Thanks in advance for helping

 

[WayneRyan]

S,

Very confusing.

Try: "Date_Sold Is Null".

I think queries & domain functions (sql in general) - use Is Null
In VBA code - use IsNull(...)

Wayne

 

[smercer]

Thanks wyane

It did not like me puting in "Not"

If DLookup("[Book_ID]", "tbl_Inventory_Each_Book", "([Book_ID]= me![Condenced_Book_ID]) and ([Customer_No]=[txt_Cust_ID]) and (Date_Sold Not IsNull)") = Me!Condenced_Book_ID Then

also tried:

If DLookup("[Book_ID]", "tbl_Inventory_Each_Book", "([Book_ID]= me![Condenced_Book_ID]) and ([Customer_No]=[txt_Cust_ID]) and (Date_Sold <> IsNull)") = Me!Condenced_Book_ID Then

and did not like that either.

Tried doing it without the "Not" and "<>" but that does not work either.

Also is the space you are using in the is null important?

 

[DB7]

If DLookup("[Book_ID]", "tbl_Inventory_Each_Book", "([Book_ID]= me![Condenced_Book_ID]) and ...

You didn't ask DLookUp for a value. & you have, one too many brackets, in your DLookUp syntax

Should be;
If IsNull(DLookup("[Book_ID]", "tbl_Inventory_Each_Book", "[Book_ID]= me![Condenced_Book_ID])) AND ...

Hope this helps, good luck!

 

[WayneRyan]

S,

Yes! Very important.

Try: "Date_Sold Is Not Null"

Wayne

 

[DB7]

Sorry, value wasn't requisite but, syntax was incorrect.

Good Luck!

 

[smercer]

Wyane: that did not work either. ***Edit*** I get error message (See attachment) ***End edit***

DB7: I'll post the entire if statement so you get an idea of why I need it like this

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 ([Date_Sold] Is not Null)") = Me!Condenced_Book_ID Then
    DoCmd.Beep
    MsgBox "You have already scanned that book"
    Call Clear_Exchange_Input
    GoTo End_me
End If

I need it like this so that when the user scans in the same book twice, they get a message. the update query changes the customer number when the user scans a book and if it is in the exchange form the Date_Sold field needs to have a date in it to be scaned in the exchange form.

Thanks for helping

 

[DB7]

smercer, my apologies,
can you say "What the #$#*& was I talking about"!

Possibly, a chance to redeem myself...

Wayne, i'm sure this was an oversight also,

Not this, "Date_Sold Is Not Null"

But, "Date_Sold Not Is Null"

If this doesn't work, Im going to lay low for a while, LOL!

Good luck, either way!

 

[WayneRyan]

S,

Totally different problem:

"([Book_ID]= '" & Me![Condenced_Book_ID] & "' And ..."

You need to add the single-quotes to delimit your string. And since it
contains spaces, it's confusing the parser.

Add the single-quotes around the string.
Keep the Is Not Null.

Wayne

 

[smercer]

Wyane: New error message see attachment

DB7: Don't worry

 

[DB7]

...Famous last words.

Wayne is correct, about the single quotes, (I saw the attachment, after the fact).
I'm quite certain about "Not Is Null", this may be why, it's being read as a value...thus, "No Operator" or "Type Mismatch". It doesn't recognize "Is Not Null" even the function, is syntaxed as such, Not IsNull(txtName)...
but in SQL, you have the spaces...



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 " & _
"([Date_Sold] Not Is Null)") = Me!Condenced_Book_ID Then

 

[WayneRyan]

S,

This should work:

Book_ID (String) - From previous post
Customer_No (Number) - I hope so, if not surround it with single-quotes.

If Not IsNull(DLookup("[Book_ID]", "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") Then
    DoCmd.Beep
    MsgBox "You have already scanned that book"
    Call Clear_Exchange_Input
    GoTo End_me
End If

Strings are surrounded by single-quote
Numbers are surrounded by nothing
Dates are surrounded by #

Otherwise, you get errors.

Also, interesting that given the initial topic, we end up with one statement that
has BOTH "Is Not Null" and "Not IsNull" in it.

Wayne

 

[smercer]

DB7: How do you make the code go to the next line like that and make it work?

 

[smercer]

DB7: your code produced an error (see attachment)

 

[DB7]

In general, when continuing code, to the next line, use Space then Underscore. With strings, you still must concatenate, so last character in code, before the line break, gets a Double quote, Ampersand, then Space and Underscore.

"SELECT * " & _
"FROM tblBooks " & _
"WHERE txtISBN = '" & cboISBN & "'" & _
"ORDER BY txtAuthor"

or

DoCmd.OpenForm "frmBooks", _
acNormal, "txtAuthor = "Edgar Allen Poe"

 

[DB7]

I DON"T BELIEVE THIS (refering to myself)...

smercer, try this please..

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

 

[smercer]

S,

This should work:

Book_ID (String) - From previous post
Customer_No (Number) - I hope so, if not surround it with single-quotes.

If Not IsNull(DLookup("[Book_ID]", "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") Then
    DoCmd.Beep
    MsgBox "You have already scanned that book"
    Call Clear_Exchange_Input
    GoTo End_me
End If

Also, interesting that given the initial topic, we end up with one statement that
has BOTH "Is Not Null" and "Not IsNull" in it.

Wayne

having the "Not IsNull" like that will return a value that is not null in the primary key field, which is compulsry to have a value in the field anyway.

The dlookup needs to check to see if the BookID is the same as the book already scanned, with a number in the Cust_ID that matches the current customer number, and has a date in the Date_Sold field.

Could I use a "Is Not Empty()" instead of "Is Not Null"?

Thanks

 

[WayneRyan]

S,

Lose the "Not Is Null", and try "Is Not Null"

Wayne

 

[DB7]

smercer, Wayne is correct, (I believe we both are).

either...
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


or

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 " & _
"([Date_Sold] Is Not Null)") = Me!Condenced_Book_ID Then


I was wrong with Not Is Null, (it's proper in the query pane, but gives different in the SQL window, where Is Not Null, remains the same.)

either way, Is Not Null, or Not[control] Is Null

 

[smercer]

DB7: 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

Wyane: Thanks for trying

Thanks very much for helping