How to check a text field which has no value asigned?

[Min]

Hi, the following query returns an error:

Select Top 1 ItemNumber from Invoice Where InvoiceID isnull Order by
ItemNumber

What I am going to check is if the InvoiceID has been assigned to this
record (text field) .

I test Len(InvoiceID) < 1, Invoice isnull, ... but all failed.

If I use "InvoiceID = nothing" ro "InvoiceID = empty", the query will ask me
to input a parameter. But no mater what value I input, it always gives no
record.

What's the correct query? I am using ADODB in MS Access 2000.

Thanks!

 

[John Spencer (MVP)]

Correct syntax would be somethng like:

SELECT TOP 1 ItemNumber
FROM Invoice
WHERE InvoiceID is Null
ORDER By ItemNumber

However, I have no idea if this will logically give you whatever you are trying
to see.

 

[Min]

Thank you for your reply!
You are right. Actually, I tried is null before. But I find something
strange:

1) Select Top 1 ItemNumber from Invoice Where InvoiceID is null Order by
ItemNumber

This returns not only 1 record but all record that contains same smallest
ItemNumbers.

2) Select Top 1 ItemNumber from Invoice Where InvoiceID is null and CompId =
8 Order by ItemNumber

This one give syntax error for the where clause.

3) Select Top 1 ItemNumber from Invoice Where CompId = 8 and InvoiceID is
null Order by ItemNumber

This one gives right result.

I don't understand why the first two have unexpected results.

Thank you very much!

Min

 

[John Vinson]

Hi, the following query returns an error:

Select Top 1 ItemNumber from Invoice Where InvoiceID isnull Order by
ItemNumber

Use

IS NULL

instead of

isnull

There is a VBA function IsNull() but the SQL operator has two words,
not one.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thank you for your reply!
You are right. Actually, I tried is null before. But I find something
strange:

1) Select Top 1 ItemNumber from Invoice Where InvoiceID is null Order by
ItemNumber

Well, this is very odd now that I think of it. I'd assume that
InvoiceID should be the Primary Key of Invoice, just from the
fieldname - and the InvoiceID can nver be NULL, by definition!

What kind of Invoice has no ID? (I have no idea!)

This returns not only 1 record but all record that contains same smallest
ItemNumbers.

The TOP predicate returns all of the records for which the sort order
is tied.

2) Select Top 1 ItemNumber from Invoice Where InvoiceID is null and CompId =
8 Order by ItemNumber

This one give syntax error for the where clause.

3) Select Top 1 ItemNumber from Invoice Where CompId = 8 and InvoiceID is
null Order by ItemNumber

This one gives right result.

That's peculiar; are you sure they were copied correctly? The order of
fields in the WHERE clause appears to be the only difference and that
should NOT cause an error.

I don't understand why the first two have unexpected results.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps