Selecting dates through a query

[Susan R]

I have a production table with 5 unqiue date fields. I am trying to develop
a query by which I can summarize data from all date fields based on one date
field. For example, if the primary date field is 5/10/05, I want to select
all records from all five date fields that have a 5/10/05 date. Any
suggestions?

 

[Rick B]

Just put the same criteria under each of the date fields. Put them on
separate lines if you wish to create an "OR" condition.

 

[Susan R]

That was the first thing I tried. I am using =[Date] in each field where
Date is the name of the field which contains the primary date. =[Date] works
in one field, but when I add that criteria to the other fields I get no
results.

 

[Rick B]

DATE is a reserved word in Access. You need to reevaluate your field names.


--
Rick B

That was the first thing I tried. I am using =[Date] in each field where
Date is the name of the field which contains the primary date. =[Date] works
in one field, but when I add that criteria to the other fields I get no
results.
--
Susan R

Just put the same criteria under each of the date fields. Put them on
separate lines if you wish to create an "OR" condition.

--
Rick B



one
date

 

[Susan R]

I changed all fields titled Date to CurrentDate and get the same results.
When the criteria is placed in one date field, such as Batch1DateComplete,
the selection is correct; however, when I place the criteria in another
field, such as Batch2DateComplete, I get no results.
--
Susan R

DATE is a reserved word in Access. You need to reevaluate your field names.


--
Rick B

That was the first thing I tried. I am using =[Date] in each field where
Date is the name of the field which contains the primary date. =[Date] works
in one field, but when I add that criteria to the other fields I get no
results.
--
Susan R

Just put the same criteria under each of the date fields. Put them on
separate lines if you wish to create an "OR" condition.

--
Rick B



I have a production table with 5 unqiue date fields. I am trying to
develop
a query by which I can summarize data from all date fields based on one
date
field. For example, if the primary date field is 5/10/05, I want to
select
all records from all five date fields that have a 5/10/05 date. Any
suggestions?

 

[Douglas J. Steele]

Are you putting the criteria on the same line in the query grid, or on
separate lines? You need to put each one on a separate line.

When you put the critieria on the same line, they're ANDed together, so
you'll only get records where Batch1DateComplete equals today AND
Batch2DateComplete equals today.

When you put them on separate lines, they're ORed together, so that you'll
get records where Batch1DateComplete equals today OR Batch2DateComplete
equals today.

Just a comment, though. The existence of fields with names like
Batch1DateComplete and Batch2DateComplete usually indicates that you haven't
normalized your tables. With a properly normalized database, you most likely
wouldn't be needing to check 5 different fields for the same value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I changed all fields titled Date to CurrentDate and get the same results.
When the criteria is placed in one date field, such as Batch1DateComplete,
the selection is correct; however, when I place the criteria in another
field, such as Batch2DateComplete, I get no results.
--
Susan R

DATE is a reserved word in Access. You need to reevaluate your field
names.


--
Rick B

That was the first thing I tried. I am using =[Date] in each field
where
Date is the name of the field which contains the primary date. =[Date] works
in one field, but when I add that criteria to the other fields I get no
results.
--
Susan R


:

Just put the same criteria under each of the date fields. Put them
on
separate lines if you wish to create an "OR" condition.

--
Rick B



I have a production table with 5 unqiue date fields. I am trying
to
develop
a query by which I can summarize data from all date fields based on one
date
field. For example, if the primary date field is 5/10/05, I want
to
select
all records from all five date fields that have a 5/10/05 date.
Any
suggestions?

 

[Larry Daugherty]

Hi Susan,

I've looked at about 4 other replies and believe your approach might
be flawed. Firstly, why do you have several fields in the same record
that capture the same data? I'm going to assume you don't really want
to do that. Whale it's possible to bang away at it and get the
results you want out of a flat-file construct, you'll probably face
the same problem again.

What is the significance of each of those date capturing fields? It
is interesting to me that you see the date field as higher in the
hierarchy than some subordinate records.

That gives me the impression that you are tracking something and that
some big event triggers a date entry and that there are one or more
records associated with each date entry.

Please post back describing what your application is intended to do -
in a broad sketch and then in a little more detail? Would you then
list your tables and their field names and any relationships between
them. Describe any queries you have working or almost working.

HTH