Query based on values in fields

[klr]

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.

 

[Allen Browne]

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

 

[Guest]

Post you query SQL statement.

 

[klr]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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.

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.

 

[Allen Browne]

Post the full SQL statement for your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

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.

 

[klr]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

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.

 

[Allen Browne]

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)));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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],
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.

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

Kim

Allen Browne wrote:
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".

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.

 

[klr]

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?

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)));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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],
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.

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

Kim

Allen Browne wrote:
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".

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.