How do I exclude displaying fields that do not meet a query

[Guest]

I have a new database that I created that has 8 date fields for each record,
along with other fileds for name, address, etc.. I created a query that will
search for dates that are within a 30 day range of today's date. This query
then creates a report that shows these dates along with other fields (name,
address, etc.)

My questions is this: how do I modify the query so that only those specific
date fields that meet my search critera will show up on the report. As it is
now, if any date in each record meets the critera, then all the date fields
show up on the report. Thanks.

 

[Guest]

Under each date field in your query criteria copy the same condition like

Field: Date1 Date2
Table: MyTable MyTable
Criteria:between [Initial_Date] and [Final_Date] between [Initial_Date]
and [Final_Date]

an so on for all 8

 

[Guest]

Yes, the query is setup to do just that. But since each record has 8 date
fields, when the query runs, It shows the entire record with all 8 dates. I
then need to manually find the one date field I need. Is there a way to
highlight (change color) the one date that meets the criteria?

Under each date field in your query criteria copy the same condition like

Field: Date1 Date2
Table: MyTable MyTable
Criteria:between [Initial_Date] and [Final_Date] between [Initial_Date]
and [Final_Date]

an so on for all 8
--
jl5000
<a href="http://joshdev.com"></a>

I have a new database that I created that has 8 date fields for each record,
along with other fileds for name, address, etc.. I created a query that will
search for dates that are within a 30 day range of today's date. This query
then creates a report that shows these dates along with other fields (name,
address, etc.)

My questions is this: how do I modify the query so that only those specific
date fields that meet my search critera will show up on the report. As it is
now, if any date in each record meets the critera, then all the date fields
show up on the report. Thanks.

 

[John Vinson]

Yes, the query is setup to do just that. But since each record has 8 date
fields, when the query runs, It shows the entire record with all 8 dates. I
then need to manually find the one date field I need. Is there a way to
highlight (change color) the one date that meets the criteria?

You can use Conditional Formatting on a Form (in A2000 or later).
BUT...

This table sounds rather badly denormalized. If a single record can
have eight dates, might it not someday have nine, or ten? Will you
need to change your table structure if that happens? Do you in fact
have a one-to-many relationship?

John W. Vinson[MVP]

 

[Guest]

I finally figured out about the conditional formatting and then added the
same filter to the the form, and it works great. Since I'm new to Access
2003, I'm not sure if the table structure I used was the best way to do it,
but it seems to work fine. The 8 date fields I needed were for our employees
that have certifications that expire over the next 2 years. The database was
simply to let me know when they will expire by highlighting the expiration
dates for the next 30-90 days in a nice looking report. I used a single
table that has 25 fields.

Thanks.