Date Is Null AND Status <>

[Albert Lacaze]

I'm hoping someone help me see the error of my ways...

I have an Access 2000 database with 2 columns configured as described
below. I have a query that should return all records that have a NULL
ReceivedDate and Status not equal to 'VOID'. Each section of the
query works appropriately by themselves, but when joined with the AND
it returns 0 records. Any help would be appreciated!

Returns all records with a NULL ReceivedDate:
SELECT Job.ReceivedDate, Job.Status
FROM Job
WHERE (((Job.ReceivedDate) Is Null));

Returns all records with status not equal to 'VOID'
SELECT Job.ReceivedDate, Job.Status
FROM Job
WHERE (((Job.Status)<>'VOID'));

Returns 0 records:
SELECT Job.ReceivedDate, Job.Status
FROM Job
WHERE (((Job.ReceivedDate) Is Null) AND ((Job.Status)<>'VOID'));

Column Configuration:
Table: Job
ReceivedDate Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Caption: Rec'd Plans
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 1260
Data Updatable: False
Format: Short Date
GUID: Long binary data
IMEMode: 0
IMESentenceMode: 3
InputMask: 99/99/00;0
Ordinal Position: 7
Required: False
Source Field: ReceivedDate
Source Table: Job

Status Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DisplayControl: Text Box
GUID: Long binary data
IMEMode: 0
IMESentenceMode: 3
Ordinal Position: 12
Required: False
Source Field: Status
Source Table: Job
UnicodeCompression: True

 

[Douglas J. Steele]

Do you in fact have any records for which both the ReceivedDate is Null and
the Status is Void? That's what your query is looking for.

 

[GreySky]

Mind if I go out on a limb and suggest that you don't have
any jobs you haven't received? Heh.

I see your Status is NOT a required field... I would ask
if "not equal to 'VOID'" also means Status could be null,
too (i.e., no status is assigned, but heck at least it's
not void). In that case your SQL would look like more
like this:

SELECT ReceivedDate, Status
FROM Job
WHERE ReceivedDate Is Null And (Status Is Null Or Status
<> 'VOID')

In other words, by saying Status <> 'VOID' DOES NOT MEAN
NULL COUNTS. It doesn't. It's null. Null is special!
Like that redhead you could never quite tame. Or that
value your ex always claimed you were.

Null's tricky value was concocted by some guy who
supposedly drew up the rules some years ago on how
relational databases are supposed to work (what was his
name again?). Bah, anyway.

GreySky

 

[Albert Lacaze]

Ok, fair questions. I've taken the query and ran it with an OR
instead of AND. This works as expected. Drum roll please...

SELECT Job.Name, Job.ReceivedDate, Job.CheckNumber, Job.Status
FROM Job
WHERE (((Job.ReceivedDate) Is Null)) OR (((Job.Status)<>'VOID'));

Name ReceivedDate Status
=====================================
Keller | |
BRIGGS | |
PAULING | 8/6/2003 0:00:00 | IVR
PACE | 8/1/2003 0:00:00 | IVR
KellerHome | | VOID

However, I need to this to be an AND. I only want records "Keller"
and "BRIGGS", which is what I expected to receive with:

SELECT Job.ReceivedDate, Job.Status
FROM Job
WHERE (((Job.ReceivedDate) Is Null) AND ((Job.Status)<>'VOID'));

 

[GreySky]

You obviously haven't tried taming a redhead, lest you
would understand that they *really are special!*

Did you not read my post? Did I not mention that NULL IS
SPECIAL!

'IVR' is a value.
'VOID' is a value.
NULL is that special value my ex claims I am, and that is
to say, it ain't a value. I mean "fly on wall" is at
least a value!

Job.Status <> 'VOID' DOES NOT MEAN THAT NULL WILL SATISFY
THIS EXPRESSION! It's NULL!

Maybe there's something you don't understand. You see
where the Status is "blank" in your Keller and BRIGGS
lines? THEY ARE NULL!

Job.Status <> 'VOID' would return PAULING AND PACE.

Job.Status Is Null Or Job.Status <> 'VOID' would return
Keller, BRIGGS, PAULING, and PACE.

Therefore, update your criteria for Status to be "Is Null
Or <> 'VOID'."

Is it clear this time? Do I need to write an algorithm
for your bank, too?

David Atkins, MCP

Wife: Honey, I don't know why we're overdrawn in the bank.
Me: I see dear, don't worry I'll write them a letter.

Letter to Bank:

Dear Bank:

I've noticed your system uses a technique
called "subtraction" when checks are drawn against our
bank account. Unfortunately, this results in our
continuously being overdrawn in our account, as it doesn't
take into account the realities of my wife's existence.

To avoid further issues, please substitute your
subtraction methods with the algorithm found below.

Sub MathForTheAtkinsAcct(curCheckAmount)

Dim mwNancy As New MyWife
Dim actAtkins As New BankAccount

' return the Atkins' bank account
Set actAtkins = actAtkins.GetAccount("Atkins, D&N")

' all deposits are counted using addition
If curCheckAmount > 0 Then
actAtkins.Balance = actAtkins.Balance + curCheckAmount
Goto Exit_MathForTheAtkinsAcct ' we're done
End If

' if Nancy is pms'ing, you can't count this check...ever
If mwNancy.PMS = True Then Goto Exit_MathForTheAtkinsAcct

' amount to deduct based upon whether she checked balance
If actAtkins.LastDateBalanceChecked = Date() Then
' if she checked it today, she only spends $1 twice
actAtkins.Balance = actAtkins.Balance _
- (curCheckAmount / 2)
Else
' if she has no clue, thrice usually is ok
actAtkins.Balance = actAtkins.Balance _
- (curCheckAmount / 3)
End If

Exit_MathForTheAtkinsAcct:
Set actAtkins = Nothing

End Sub

Any consideration you would give to implementing the above
alogorithm would truly make my life a lot easier, as it
should clear up our unexpected overdraft issues.

David Atkins

 

[Albert Lacaze]

Ok, thanks for hitting me over the head with it.