Date in criteria not processing all date records

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

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

Re: Date in criteria not processing all date records

"Rick Brandt" wrote in message
news:dyGNj.5317$GO4.3888@newssvr19.news.prodigy.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 > > 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 may prevent some future problems.

Re: Date in criteria not processing all date records

On Apr 17, 5:00=A0pm, "paii, Ron" wrote:
> "Rick Brandt" wrote in message
>
> news:dyGNj.5317$GO4.3888@newssvr19.news.prodigy.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 > > > 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 thedateindatefield 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 thecriteriaunder all the fields on the same row in the query=

> > designer then you will only get rows where ALL of the dates are older th=
an
> > 180 days. =A0Is 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 =3D AND
> > Separate rows =3D OR
>
> > --
> > Rick Brandt, Microsoft Access MVP
> > Email (as appropriate) to...
> > RBrandt =A0 at =A0 Hunter =A0 dot =A0 com
>
> Adding to Rick's post, replacing )
> may prevent some future problems.- Hide quoted text -
>
> - Show quoted text -

I tried 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
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

Re: Date in criteria not processing all date records

On Thu, 17 Apr 2008 19:51:21 -0700 (PDT), Dr Al
wrote:

>On Apr 17, 5:00 pm, "paii, Ron" wrote:
>> "Rick Brandt" wrote in message
>>
>> news:dyGNj.5317$GO4.3888@newssvr19.news.prodigy.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 >> > > 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 thedateindatefield 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 thecriteriaunder 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 >> may prevent some future problems.- Hide quoted text -
>>
>> - Show quoted text -
>
>I tried >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
> >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

Re: Date in criteria not processing all date records

"Dr Al" wrote in message
news:3969f22d-520e-4fa6-8a4f-02e29a8dba8c@m71g2000hse.google groups.com...
On Apr 17, 5:00 pm, "paii, Ron" wrote:
> "Rick Brandt" wrote in message
>
> news:dyGNj.5317$GO4.3888@newssvr19.news.prodigy.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 > > > 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 thedateindatefield 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 thecriteriaunder 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 > may prevent some future problems.- Hide quoted text -
>
> - Show quoted text -

I tried 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
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.Date1) Is Not Null And
(tablename.Date1) Null And (tablename.Date2) Is Not Null And (tablename.Date3) (((tablename.Date4) Is Not Null And
(tablename.Date4)

Re: Date in criteria not processing all date records

paii, Ron wrote:
> I tried > 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
> > 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

Re: Date in criteria not processing all date records

On Apr 18, 7:38=A0pm, "Rick Brandt" wrote:
> paii, Ron wrote:
> > I tried > > 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
> > > > 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. =A0Perhaps =
if
> you also posted a few examples of records you want the query to return alo=
ng
> with a few that the query should not return.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt =A0 at =A0 Hunter =A0 dot =A0 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]) Letter]) Letter]) Letter])
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.

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]) > Letter]) > Letter]) > Letter]) >
> 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

Re: Date in criteria not processing all date records

On Apr 19, 9:56=A0am, "Rick Brandt" wrote:
> 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]) > > Letter]) > > Letter]) > > Letter]) >
> > 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 =A0 =A0Date of 1st Letter Date of 2nd Letter Date of 3rd
> > Letter Date of 4th Letter
> > 2 East =A0 =A0 =A0 =A01/1/2006 1/1/2007 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A01/1/2008
> > 3 Martin =A0 =A0 =A0 =A0 3/1/2008 3/15/2008
> > 3/30/2008 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 4/15/2008
> > 4 Wilson =A0 =A0 =A0 10/1/2007 10/16/2007 11/1/2007
> > 11/16/2007
> > 5 Rolek =A0 =A0 =A0 12/1/2007 1/1/2008 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A02/1/2008
> > 11 Tosky =A0 =A0 =A0 =A0 3/1/2008 3/16/2008 =A0 =A0 =A0 =A0 =A0 4/16/200=
8
> > 9 Foster =A0 =A0 =A0 =A0 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. =A0That is what an OR criteria as you have constructed will
> produce.
>
> So...what is the result you DO want? =A0It sounds like you want all record=
s
> having NO date newer than 14 days ago. =A0That would be accomplished with =
AND
> rather than OR, but then you have to account for Nulls in your fields whic=
h
> 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. =A0Any tim=
e
> 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. =A0Then 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" =A0you can add as many as you want bec=
ause
> 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 =A0 at =A0 Hunter =A0 dot =A0 com- Hide quoted text -
>
> - Show quoted text -

Thanks Rick,

I decided to create a separate table to hold the date fields. The
table has 5 date fields in the medium date format. I do not understand
why this sql is not working to get the max date.

SELECT Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1, Table1.LETTER2,
Table1.LETTER3, Table1.LETTER4, Max(Table1.NEW) AS MaxOfNEW
FROM Table1
GROUP BY Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1,
Table1.LETTER2, Table1.LETTER3, Table1.LETTER4
ORDER BY Table1.[LAST-NAME];

Thanks

Re: Date in criteria not processing all date records

Dr Al wrote:
> Thanks Rick,
>
> I decided to create a separate table to hold the date fields. The
> table has 5 date fields in the medium date format. I do not understand
> why this sql is not working to get the max date.
>
> SELECT Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1, Table1.LETTER2,
> Table1.LETTER3, Table1.LETTER4, Max(Table1.NEW) AS MaxOfNEW
> FROM Table1
> GROUP BY Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1,
> Table1.LETTER2, Table1.LETTER3, Table1.LETTER4
> ORDER BY Table1.[LAST-NAME];

All you did was move your incorrect design to another table. Your new table
should have ONE date field. Sample data would be...

ID1 LAST-NAME LETTER-DATE
1 Smith 2/4/2008
1 Smith 3/14/2008
2 Jones 3/13/2008
3 Doe 1/3/2008
3 Doe 2/5/2008
3 Doe 3/12/2008

Now you can use a query that Groups by ID1 and LastName showing the Max of
the LETTER-DATE field and you will have the most recent date that a letter
was sent per ID1/Last-Name combination. That query can have a criteria that
only shows Max Dates older than 14 days.

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

Re: Date in criteria not processing all date records

On Apr 21, 9:08=A0pm, "Rick Brandt" wrote:
> Dr Al wrote:
> > Thanks Rick,
>
> > I decided to create a separate table to hold the date fields. The
> > table has 5 date fields in the medium date format. I do not understand
> > why this sql is not working to get the max date.
>
> > SELECT Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1, Table1.LETTER2,
> > Table1.LETTER3, Table1.LETTER4, Max(Table1.NEW) AS MaxOfNEW
> > FROM Table1
> > GROUP BY Table1.ID1, Table1.[LAST-NAME], Table1.LETTER1,
> > Table1.LETTER2, Table1.LETTER3, Table1.LETTER4
> > ORDER BY Table1.[LAST-NAME];
>
> All you did was move your incorrect design to another table. =A0Your new t=
able
> should have ONE date field. =A0Sample data would be...
>
> ID1 =A0 =A0LAST-NAME =A0 =A0LETTER-DATE
> 1 =A0 =A0 =A0 =A0Smith =A0 =A0 =A0 =A0 =A0 =A02/4/2008
> 1 =A0 =A0 =A0 =A0Smith =A0 =A0 =A0 =A0 =A0 =A03/14/2008
> 2 =A0 =A0 =A0 =A0Jones =A0 =A0 =A0 =A0 =A0 =A03/13/2008
> 3 =A0 =A0 =A0 =A0Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1/3/2008
> 3 =A0 =A0 =A0 =A0Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2/5/2008
> 3 =A0 =A0 =A0 =A0Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 3/12/2008
>
> Now you can use a query that Groups by =A0ID1 and LastName showing the Max=
of
> the LETTER-DATE field and you will have the most recent date that a letter=

> was sent per ID1/Last-Name combination. =A0That query can have a criteria =
that
> only shows Max Dates older than 14 days.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt =A0 at =A0 Hunter =A0 dot =A0 com

Ok, I'm getting it.

Thanks