Query: Two Dates in one cell of a field. Need criteria help

[running381]

I have a query that has a field called Date Filed/Rec'd and it
includes two dates. Some dates include the day of the month and some don't
because the day of the month is unknown. For example:
12/2/2004 12/23/2004
5/17/1998 1/10/1999
3/98 4/98
4/17/1999 5/1999
4/2006 4/21/2006
I know that it is possible to find out which year all records were either
filed or received for 2004, which is by typing Like "*2004*" but my
problem is when I have to find any records that were filed or received
between 2003 and 2005. I also have other criteria that I have to meet in
other fields. But I would like to meet that criteria for several years. I
know that i can copy the criteria for my other fields in the year 2004 and
just change the Date Filed/Rec'd field to Like "*2003*" and then the
field on the next row to Like "*2005*" . I would get my results
this way but later on I will need the range between 1996 to 2006 and I will
have to type in criteria for 10 rows, which is very tedious. Can someone
please help me fix this problem. Thanks in advance!

 

[Guest]

You really, really need to fix that table. A field or column (not cell)
should have one, and only one, piece of information in it. You need a
DateFiled column and a DateRecd column. They both need to be Date/Time
datatypes. For your existing data that is missing the day, just make it the
first day of the month.

Then your query would be very easy to do.

 

[John Spencer]

And further is your date field is approximate (unknown day) you can use an
additional field that is set to true when the date is an approximation.

Another way to handle this is to store each component of each date in separate fields
FiledYear
FiledMonth
FiledDay
RecdYear
RecdMonth
RecdDay

You can then use the components individually or use them in combination to build
a date that you can search against.