Query based on values in fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

klr - 18 Jul 2006 16:28 GMT

I have a report based on a table, and want to set up a query to limit
the records pulled through.

The report will be run at the beginning of the month, and needs to pull
through all records from previous month based on date field - I am
using Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0) which works fine.

I now have 3 other fields entitled IntNow; IntFut and IntRec. One or
more of these fields may be populated therefore the query needs to look
at these 3 fields to see if they are populated and if the record is
dated last month then it is pulled through to the report.

The query is structured so that the date field appears AFTER IntNow;
IntFut and IntRec which have Is Not Null in the Criteria field (as Or),
and the expression for the date field is entered in all criteria rows.

The query seems to function however I am finding that 2 records are
being pulled through that although dated last month, have nothing in
the IntNow; IntFut and IntRec fields.

I can't understand why this should be happening. Any ideas gratefully
received.

Reply to this Message

Allen Browne - 18 Jul 2006 16:47 GMT

This sounds like the combination of AND and OR in the WHERE clause is the
problem.

Switch the query to SQL View (View menu), and change the WHERE clause so it
is bracketed like this:

WHERE ([MyDate] Between DateSerial(Year(Date()),Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),0))
AND NOT (IntNow Is Null AND IntFut Is Null AND IntRec Is Null)

It is important to understand that:
(a AND b) OR c
does not give the same results as:
a AND (b OR c)
where a, b, and c are phrases such as "MyField = 6".

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a report based on a table, and want to set up a query to limit
> the records pulled through.
[quoted text clipped - 19 lines]
> I can't understand why this should be happening. Any ideas gratefully
> received.

Reply to this Message

klr - 19 Jul 2006 12:26 GMT

Have tried - am still getting the same result - it's a puzzler!

Kim

> This sounds like the combination of AND and OR in the WHERE clause is the
> problem.
[quoted text clipped - 40 lines]
> > I can't understand why this should be happening. Any ideas gratefully
> > received.

Reply to this Message

Allen Browne - 19 Jul 2006 13:48 GMT

Post the full SQL statement for your query.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Have tried - am still getting the same result - it's a puzzler!
>
[quoted text clipped - 40 lines]
>> > I can't understand why this should be happening. Any ideas gratefully
>> > received.

Reply to this Message

klr - 19 Jul 2006 15:09 GMT

SELECT t_all_reports.[Report ID], t_all_reports.[Action Points],
t_all_reports.[IntNow], t_all_reports.[IntFut], t_all_reports.[IntRec],
t_all_reports.[Date_ent], t_all_reports.Comments
FROM t_all_reports
WHERE ([Date_ent] Between DateSerial(Year(Date()),Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),0))
AND NOT (IntNow Is Null AND IntFut Is Null AND IntRec Is Null)

> Post the full SQL statement for your query.
>
[quoted text clipped - 47 lines]
> >> > I can't understand why this should be happening. Any ideas gratefully
> >> > received.

Reply to this Message

Allen Browne - 19 Jul 2006 17:19 GMT

So, the dates are right, but 2 records are being included where all 3 of the
fields (IntNow, IntFut, and IntRec) are blank?

Let's ask Access to tell us whether those fields are in fact Null.
Try the query below, and see if the last 3 columns all say True on the 2
problem records:

SELECT t_all_reports.[Report ID],
t_all_reports.[Action Points],
t_all_reports.[IntNow],
t_all_reports.[IntFut],
t_all_reports.[IntRec],
t_all_reports.[Date_ent],
t_all_reports.Comments,
(IntNow Is Null) AS IntNowIsNull,
(IntFut Is Null) AS IntFutIsNull,
(IntRec Is Null) AS IntRecIsNull
FROM t_all_reports
WHERE (([Date_ent] Between
CDate(DateSerial(Year(Date()),Month(Date())-1,1))
And CDate(DateSerial(Year(Date()),Month(Date()),0)))
AND NOT ((IntNow Is Null) AND (IntFut Is Null) AND (IntRec Is Null)));

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> SELECT t_all_reports.[Report ID], t_all_reports.[Action Points],
> t_all_reports.[IntNow], t_all_reports.[IntFut], t_all_reports.[IntRec],
[quoted text clipped - 58 lines]
>> >> > gratefully
>> >> > received.

Reply to this Message

klr - 20 Jul 2006 14:25 GMT

There WAS data in the record. The User had placed a carriage return at
the start of the paragraph, therefore the query result looked as though
the fields were empty. Sorry to have wasted your time, however it was
useful to use SQL code as that is something I have never done before.

Thanks again.

> So, the dates are right, but 2 records are being included where all 3 of the
> fields (IntNow, IntFut, and IntRec) are blank?
[quoted text clipped - 86 lines]
> >> >> > gratefully
> >> >> > received.

Reply to this Message

KARL DEWEY - 18 Jul 2006 17:00 GMT

Post you query SQL statement.

> I have a report based on a table, and want to set up a query to limit
> the records pulled through.
[quoted text clipped - 19 lines]
> I can't understand why this should be happening. Any ideas gratefully
> received.

Reply to this Message