Query incorrectly reporting a value as null

[CW]

I have a very simple query based on our Quotes table, which includes a field
called BookedDate, and it displays a few basic columns for those records
where this is empty (criterion for BookedDate is "Is Null").

It insists on including one particular record where the date is perfectly
well held in the table. I have tried deleting that date, saving, then
re-entering it. I have done a compact and repair. However, despite the field
being populated, Access insists on saying that it isn't.

Wot's going on???

Many thanks
CW

 

[Dirk Goldgar]

I have a very simple query based on our Quotes table, which includes a
field
called BookedDate, and it displays a few basic columns for those records
where this is empty (criterion for BookedDate is "Is Null").

It insists on including one particular record where the date is perfectly
well held in the table. I have tried deleting that date, saving, then
re-entering it. I have done a compact and repair. However, despite the
field
being populated, Access insists on saying that it isn't.

Wot's going on???

That's peculiar. Please post the SQL of the query.

 

[KARL DEWEY]

When it displays the record does it indicate the date in the query results?

Is it possible that you have duplicate records - one with date and one
without?

 

[CW]

Dirk -
Here's the SQL. As you'll see I mis-named the table in question in my post,
it is
Inquiries not Quotes...many thanks for looking into this
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (((Inquiries.Status)="Booked" Or (Inquiries.Status)="2") AND
((Events.BookedDate) Is Null))
ORDER BY Inquiries.Ref DESC;

 

[CW]

Karl -
No, it simply shows a blank.
I have checked the tables and no, there isn't a duplicate record.
Thanks for your response
CW

 

[KARL DEWEY]

I would be afraid to use 'TO' and 'FROM' in my query.
Try it this way --
SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (Inquiries.Status="Booked" Or Inquiries.Status="2") AND
(Events.BookedDate Is Null)
ORDER BY Inquiries.Ref DESC;

If still the same then try this --
SELECT Inquiries.Ref, Inquiries.Customer1Surname, Inquiries.CorpName,
Inquiries.OriginAddr03, Inquiries.DestAddr03, Inquiries.InquiryDate,
Inquiries.Status, Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (Events.BookedDate Is Null)
ORDER BY Inquiries.Ref DESC;

Or this --
SELECT Inquiries.Ref, Inquiries.Customer1Surname, Inquiries.CorpName,
Inquiries.OriginAddr03, Inquiries.DestAddr03, Inquiries.InquiryDate,
Inquiries.Status, Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
ORDER BY Inquiries.Ref DESC;

--
Build a little, test a little.

Dirk -
Here's the SQL. As you'll see I mis-named the table in question in my post,
it is
Inquiries not Quotes...many thanks for looking into this
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (((Inquiries.Status)="Booked" Or (Inquiries.Status)="2") AND
((Events.BookedDate) Is Null))
ORDER BY Inquiries.Ref DESC;

That's peculiar. Please post the SQL of the query.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[John W. Vinson]

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (((Inquiries.Status)="Booked" Or (Inquiries.Status)="2") AND
((Events.BookedDate) Is Null))
ORDER BY Inquiries.Ref DESC;

With the LEFT JOIN in the query... you'll get a record with BookedDate being
NULL whenever there is no matching value of Ref in the Events table.
Doublecheck the problem record - does its Ref value exist in the Events table?

 

[Dirk Goldgar]

Dirk -
Here's the SQL. As you'll see I mis-named the table in question in my
post,
it is
Inquiries not Quotes...many thanks for looking into this
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (((Inquiries.Status)="Booked" Or (Inquiries.Status)="2") AND
((Events.BookedDate) Is Null))
ORDER BY Inquiries.Ref DESC;

You said that the table Quotes -- now known to be Inquiries -- contained the
field BookedDate, but it doesn't. That field is in the related table
Events, and will only be picked up by the query where the Events record
matches the Inquiries record on the Ref field. Do you also have a field
named "BookedDate" in table Inquiries, which could be misleading indeed?

Because of the left join, this query will return a record for every
Inquiries record with Status = "Booked" or "2" -- an odd combination of
values -- *even if* the record has no match in Events. In such cases, the
BookedDate field returned by the query will be Null. My guess is that your
Inquiries record doesn't match a record in Events. If you think there is a
record in Events that it should match, look closely at the Ref field in both
tables. Maybe there's a discrepancy there.

 

[CW]

Dirk -
I found the problem - there was, after all, a dupe record in the Events
table. I hadn't spotted it before cos the records were sorted by ID and not
by Ref so it hadn't jumped out at me.
I apologise for sending you on a wild goose chase and wasting your time, but
at least your helpful suggestions have taught me a few things in what to look
for in tracking down the problem.
Many thanks
CW

Dirk -
Here's the SQL. As you'll see I mis-named the table in question in my
post,
it is
Inquiries not Quotes...many thanks for looking into this
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Customer,
Inquiries.CorpName AS Corp, Inquiries.OriginAddr03 AS [From],
Inquiries.DestAddr03 AS [To], Inquiries.InquiryDate, Inquiries.Status,
Events.BookedDate
FROM Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref
WHERE (((Inquiries.Status)="Booked" Or (Inquiries.Status)="2") AND
((Events.BookedDate) Is Null))
ORDER BY Inquiries.Ref DESC;

You said that the table Quotes -- now known to be Inquiries -- contained the
field BookedDate, but it doesn't. That field is in the related table
Events, and will only be picked up by the query where the Events record
matches the Inquiries record on the Ref field. Do you also have a field
named "BookedDate" in table Inquiries, which could be misleading indeed?

Because of the left join, this query will return a record for every
Inquiries record with Status = "Booked" or "2" -- an odd combination of
values -- *even if* the record has no match in Events. In such cases, the
BookedDate field returned by the query will be Null. My guess is that your
Inquiries record doesn't match a record in Events. If you think there is a
record in Events that it should match, look closely at the Ref field in both
tables. Maybe there's a discrepancy there.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk -
I found the problem - there was, after all, a dupe record in the Events
table. I hadn't spotted it before cos the records were sorted by ID and
not
by Ref so it hadn't jumped out at me.
I apologise for sending you on a wild goose chase and wasting your time,
but
at least your helpful suggestions have taught me a few things in what to
look
for in tracking down the problem.

You're welcome. It sounds like Karl Dewey had the right angle on the
problem from the beginning.