Date in criteria not processing all date records

[Dr Al]

I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use that criteria for all four fields I am not getting
the expected results.

I am trying to find out from this query is the date in date field one
is older than 180, same thing for the other three date fields. For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the first date field, after that it is
dropping relevant records.

Can someone please explain how to get the criteria in the query to
produce the results that I am looking for.

Thanks

[Rick Brandt]

Re: Date in criteria not processing all date records

Dr Al wrote:

I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use that criteria for all four fields I am not getting
the expected results.
>
I am trying to find out from this query is the date in date field one
is older than 180, same thing for the other three date fields. For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the first date field, after that it is
dropping relevant records.
>
Can someone please explain how to get the criteria in the query to
produce the results that I am looking for.
>
Thanks

If you have the criteria under all the fields on the same row in the query
designer then you will only get rows where ALL of the dates are older than
180 days. Is that what you want?

If you want all rows where ANY of the dates is older than 180 days then the
criteria need to be on separate rows.

Same row = AND
Separate rows = OR

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[paii, Ron]

Re: Date in criteria not processing all date records


"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:dyGNj.5317 $GO4.3888@newss vr19.news.prodi gy.net...

Dr Al wrote:

I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use that criteria for all four fields I am not getting
the expected results.

I am trying to find out from this query is the date in date field one
is older than 180, same thing for the other three date fields. For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the first date field, after that it is
dropping relevant records.

Can someone please explain how to get the criteria in the query to
produce the results that I am looking for.

Thanks

>
If you have the criteria under all the fields on the same row in the query
designer then you will only get rows where ALL of the dates are older than
180 days. Is that what you want?
>
If you want all rows where ANY of the dates is older than 180 days then

the

criteria need to be on separate rows.
>
Same row = AND
Separate rows = OR
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
>
>

Adding to Rick's post, replacing <date()-180 with <dateadd("d",-180,date())
may prevent some future problems.

[Dr Al]

Re: Date in criteria not processing all date records

On Apr 17, 5:00 pm, "paii, Ron" <n...@no.comwro te:

"Rick Brandt" <rickbran...@ho tmail.comwrote in message
>
news:dyGNj.5317 $GO4.3888@newss vr19.news.prodi gy.net...
>
>
>

Dr Al wrote:

I have a table with fourdatefields, some of which may not be filled
in based on our data entry needs. I have acriteriaset as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use thatcriteriafor all four fields I am not getting
the expected results.

>

I am trying to find out from this query is thedateindatefi eld one
is older than 180, same thing for the other threedatefields . For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the firstdatefield, after that it is
dropping relevant records.

>

Can someone please explain how to get thecriteriain the query to
produce the results that I am looking for.

>

Thanks

>

If you have thecriteriaunde r all the fields on the same row in the query
designer then you will only get rows where ALL of the dates are older than
180 days.  Is that what you want?

>

If you want all rows where ANY of the dates is older than 180 days then

the

criterianeed to be on separate rows.

>

Same row = AND
Separate rows = OR

>

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

>
Adding to Rick's post, replacing <date()-180 with <dateadd("d",-180,date())
may prevent some future problems.- Hide quoted text -
>
- Show quoted text -

I tried <date()180 on a seperate row for each date record, but it is
dropping records that may only have one or two date records populated.
At that point records in the third and fourth date field are picking
up fields with dates that are less than 180. I did try the
<dateadd("d",-180,date()), on a seperate line but that is giving the
same results, it is dropping fields that may be null and picking up
fields in date 3 or date 4 that are less than 180.

I am trying to find out which records based on date fields 1 through 4
are greater than 180 days.

Thanks

[Chuck]

Re: Date in criteria not processing all date records

On Thu, 17 Apr 2008 19:51:21 -0700 (PDT), Dr Al <grailchaser@ho tmail.com>
wrote:

>On Apr 17, 5:00 pm, "paii, Ron" <n...@no.comwro te:

>"Rick Brandt" <rickbran...@ho tmail.comwrote in message
>>
>news:dyGNj.531 7$GO4.3888@news svr19.news.prod igy.net...
>>
>>
>>

Dr Al wrote:
I have a table with fourdatefields, some of which may not be filled
in based on our data entry needs. I have acriteriaset as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use thatcriteriafor all four fields I am not getting
the expected results.

>>

I am trying to find out from this query is thedateindatefi eld one
is older than 180, same thing for the other threedatefields . For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the firstdatefield, after that it is
dropping relevant records.

>>

Can someone please explain how to get thecriteriain the query to
produce the results that I am looking for.

>>

Thanks

>>

If you have thecriteriaunde r all the fields on the same row in the query
designer then you will only get rows where ALL of the dates are older than
180 days.  Is that what you want?

>>

If you want all rows where ANY of the dates is older than 180 days then

>the

>criterianeed to be on separate rows.

>>

Same row = AND
Separate rows = OR

>>

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

>>
>Adding to Rick's post, replacing <date()-180 with <dateadd("d",-180,date())
>may prevent some future problems.- Hide quoted text -
>>
>- Show quoted text -

>
>I tried <date()180 on a seperate row for each date record, but it is
>dropping records that may only have one or two date records populated.
>At that point records in the third and fourth date field are picking
>up fields with dates that are less than 180. I did try the
><dateadd("d" ,-180,date()), on a seperate line but that is giving the
>same results, it is dropping fields that may be null and picking up
>fields in date 3 or date 4 that are less than 180.
>
>I am trying to find out which records based on date fields 1 through 4
>are greater than 180 days.
>
>Thanks

Check all the dates in all the date fields in the table(s). Make sure that the
person entering the dates can not enter dd/mm/yyyy or mm/dd/yy if your program
is expecting to see mm/dd/yyyy. Data entry is critical. Garbage in garbage
out.

Chuck

[paii, Ron]

Re: Date in criteria not processing all date records


"Dr Al" <grailchaser@ho tmail.comwrote in message
news:3969f22d-520e-4fa6-8a4f-02e29a8dba8c@m7 1g2000hse.googl egroups.com...
On Apr 17, 5:00 pm, "paii, Ron" <n...@no.comwro te:

"Rick Brandt" <rickbran...@ho tmail.comwrote in message
>
news:dyGNj.5317 $GO4.3888@newss vr19.news.prodi gy.net...
>
>
>

Dr Al wrote:

I have a table with fourdatefields, some of which may not be filled
in based on our data entry needs. I have acriteriaset as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use thatcriteriafor all four fields I am not getting
the expected results.

>

I am trying to find out from this query is thedateindatefi eld one
is older than 180, same thing for the other threedatefields . For
some reason it is not picking up all the older than 180 records. It
seems to be working fine for the firstdatefield, after that it is
dropping relevant records.

>

Can someone please explain how to get thecriteriain the query to
produce the results that I am looking for.

>

Thanks

>

If you have thecriteriaunde r all the fields on the same row in the query
designer then you will only get rows where ALL of the dates are older

than

180 days. Is that what you want?

>

If you want all rows where ANY of the dates is older than 180 days then

the

criterianeed to be on separate rows.

>

Same row = AND
Separate rows = OR

>

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

>
Adding to Rick's post, replacing <date()-180 with

<dateadd("d",-180,date())

may prevent some future problems.- Hide quoted text -
>
- Show quoted text -

I tried <date()180 on a seperate row for each date record, but it is
dropping records that may only have one or two date records populated.
At that point records in the third and fourth date field are picking
up fields with dates that are less than 180. I did try the
<dateadd("d",-180,date()), on a seperate line but that is giving the
same results, it is dropping fields that may be null and picking up
fields in date 3 or date 4 that are less than 180.

I am trying to find out which records based on date fields 1 through 4
are greater than 180 days.

Thanks

You need to account for the NULL values. The query's WHERE clause may need
to be like the following, using your table and field names. Assuming your
date fields are Date/Time fields and not string.

WHERE (((tablename.Da te1) Is Not Null And
(tablename.Date 1)<DateAdd("d",-180,Date()))) OR (((tablename.Da te2) Is Not
Null And (tablename.Date 2)<DateAdd("d",-180,Date()))) OR (((tablename.Da te3)
Is Not Null And (tablename.Date 3)<DateAdd("d",-180,Date()))) OR
(((tablename.Da te4) Is Not Null And
(tablename.Date 4)<DateAdd("d",-180,Date())));

[Rick Brandt]

Re: Date in criteria not processing all date records

paii, Ron wrote:

I tried <date()180 on a seperate row for each date record, but it is
dropping records that may only have one or two date records populated.
At that point records in the third and fourth date field are picking
up fields with dates that are less than 180. I did try the
<dateadd("d",-180,date()), on a seperate line but that is giving the
same results, it is dropping fields that may be null and picking up
fields in date 3 or date 4 that are less than 180.
>
I am trying to find out which records based on date fields 1 through 4
are greater than 180 days.

Please clarify this last statement as it makes no sense to me. Perhaps if
you also posted a few examples of records you want the query to return along
with a few that the query should not return.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Dr Al]

Re: Date in criteria not processing all date records

On Apr 18, 7:38 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

paii, Ron wrote:

I tried <date()180 on a seperate row for each date record, but it is
dropping records that may only have one or two date records populated.
At that point records in the third and fourth date field are picking
up fields with dates that are less than 180. I did try the
<dateadd("d",-180,date()), on a seperate line but that is giving the
same results, it is dropping fields that may be null and picking up
fields in date 3 or date 4 that are less than 180.

>

I am trying to find out which records based on date fields 1 through 4
are greater than 180 days.

>
Please clarify this last statement as it makes no sense to me.  Perhaps if
you also posted a few examples of records you want the query to return along
with a few that the query should not return.
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

Here is what the sql code looks like for the 14 day query:

SELECT [Primary Table].ID, [Primary Table].[Last Name], [Primary
Table].[Date of 1st Letter], [Primary Table].[Date of 2nd Letter],
[Primary Table].[Date of 3rd Letter], [Primary Table].[Date of 4th
Letter]
FROM [Primary Table]
WHERE ((([Primary Table].[Date of 1st
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 2nd
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 3rd
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 4th
Letter])<DateAdd("d",-14,Date())));

Here are the results of the query based upon the above. I did not
expect record "Tosky" to show up because it is clearly not valid for
the 14 day argument, same thing with record "Martin". I have the
criteria on a seperate row under each date field.

ID Last Name Date of 1st Letter Date of 2nd Letter Date of 3rd
Letter Date of 4th Letter
2 East 1/1/2006 1/1/2007 1/1/2008
3 Martin 3/1/2008 3/15/2008
3/30/2008 4/15/2008
4 Wilson 10/1/2007 10/16/2007 11/1/2007
11/16/2007
5 Rolek 12/1/2007 1/1/2008 2/1/2008
11 Tosky 3/1/2008 3/16/2008 4/16/2008
9 Foster 3/9/2008

Thanks.

[Rick Brandt]

Re: Date in criteria not processing all date records

Dr Al wrote:

Here is what the sql code looks like for the 14 day query:
>
SELECT [Primary Table].ID, [Primary Table].[Last Name], [Primary
Table].[Date of 1st Letter], [Primary Table].[Date of 2nd Letter],
[Primary Table].[Date of 3rd Letter], [Primary Table].[Date of 4th
Letter]
FROM [Primary Table]
WHERE ((([Primary Table].[Date of 1st
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 2nd
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 3rd
Letter])<DateAdd("d",-14,Date()))) OR ((([Primary Table].[Date of 4th
Letter])<DateAdd("d",-14,Date())));
>
Here are the results of the query based upon the above. I did not
expect record "Tosky" to show up because it is clearly not valid for
the 14 day argument, same thing with record "Martin". I have the
criteria on a seperate row under each date field.
>
ID Last Name Date of 1st Letter Date of 2nd Letter Date of 3rd
Letter Date of 4th Letter
2 East 1/1/2006 1/1/2007 1/1/2008
3 Martin 3/1/2008 3/15/2008
3/30/2008 4/15/2008
4 Wilson 10/1/2007 10/16/2007 11/1/2007
11/16/2007
5 Rolek 12/1/2007 1/1/2008 2/1/2008
11 Tosky 3/1/2008 3/16/2008 4/16/2008
9 Foster 3/9/2008

Assuming you ran this query today (4/19/2008) then all of those records
satisfy your query because they all have "at least one" date that is older
than 14 days. That is what an OR criteria as you have constructed will
produce.

So...what is the result you DO want? It sounds like you want all records
having NO date newer than 14 days ago. That would be accomplished with AND
rather than OR, but then you have to account for Nulls in your fields which
complicates things because now you need to have both ORs and ANDs with
proper bracketing...

WHERE (foo1 < bar OR foo1 is Null)
AND (foo2 < bar OR foo2 is Null)
AND (foo3 < bar OR foo3 is Null)
AND (foo4 < bar OR foo4 is Null)

Frankly the difficulty in the query stems from improper design. Any time
you have repeating similar fields Date1, Date2, etc.. that is incorrect.
Your Dates should be stored as individual records in a separate table
related to this one. Then all you have to do is look at the most recent
(Max) date for each name and see which of those are more than 14 days old.

That design is also more flexible because you don't have to design in a
fixed number of "sent letter dates" you can add as many as you want because
the information only requires new *rows* of data instead of new *columns*
(the latter requiring design changes).


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com