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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Quickpath - 07 May 2005 01:50 GMT

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.

Reply to this Message

jl5000 - 07 May 2005 02:04 GMT

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

Signature

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
[quoted text clipped - 6 lines]
> now, if any date in each record meets the critera, then all the date fields
> show up on the report. Thanks.

Reply to this Message

Quickpath - 07 May 2005 05:33 GMT

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
>
[quoted text clipped - 15 lines]
> > now, if any date in each record meets the critera, then all the date fields
> > show up on the report. Thanks.

Reply to this Message

John Vinson - 07 May 2005 23:46 GMT

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

Reply to this Message

Quickpath - 08 May 2005 03:21 GMT

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.

> >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
[quoted text clipped - 10 lines]
>
> John W. Vinson[MVP]

Reply to this Message