One parameter for various columns of dates

[Guest]

I have a table that has 4 columns which contain dates. I set up a query that
has those dates in it and want one parameter to search for the date ranges in
each of those columns.

So basically I want to be able to type a beginning date (i.e. 1/1/00) and
ending date (i.e. 1/1/01) in a parameter and have that display the dates from
each column that fit that criteria.

I can get it to work for one column of dates that fit the parameter that I
have set. But I cannot figure out how to have one parameter work for all of
the columns containing dates. If you have any ideas or suggestions on how to
have one parameter for various fields it would be greatly appreciated.
Thanks.

 

[Steve Schapel]

Wade,

Just put the parameter exactly the same in all of the date columns. For
example...
Between [Start date] And [End date]

You should only get prompted once for the parameter values, but what you
enter will be applied to all fields.

 

[Guest]

Thank you Steve. I still have a problem though. By setting the same
parameter for all 4 columns that means that if each column of that client is
not in that range then it doesn't show up at all in the query.

Each of the date columns contains different information. So what I
ultimately want to happen is to produce a query that will give me the clients
that have dates within the range I set in the parameter in any of the 4
columns. I don't them with all 4 columns. Just as any of the 4. I hope I
am not confusing anyone with this. Any help or suggestions would be great.
Thanks.

Wade.

Wade,

Just put the parameter exactly the same in all of the date columns. For
example...
Between [Start date] And [End date]

You should only get prompted once for the parameter values, but what you
enter will be applied to all fields.

--
Steve Schapel, Microsoft Access MVP

I have a table that has 4 columns which contain dates. I set up a query that
has those dates in it and want one parameter to search for the date ranges in
each of those columns.

So basically I want to be able to type a beginning date (i.e. 1/1/00) and
ending date (i.e. 1/1/01) in a parameter and have that display the dates from
each column that fit that criteria.

I can get it to work for one column of dates that fit the parameter that I
have set. But I cannot figure out how to have one parameter work for all of
the columns containing dates. If you have any ideas or suggestions on how to
have one parameter for various fields it would be greatly appreciated.
Thanks.

 

[Steve Schapel]

Wade,

In the query design grid, put the Criteria entry on a different line for
each of the columns.

 

[Guest]

Steve.

Thank you again. However that didn't work or I didn't do it correctly. I
hope that this is possible to accomplish. I want to enter one parameter, a
date range (start, finish), and have all the dates in 4 different columns to
show up. Thanks Steve and hopefully we can work this out.

 

[Steve Schapel]

Wade,

I think I need to see what you have tried. Can you go to the View menu
in the query design, select SQL, and then copy/paste the SQL of the
query into your reply? Thanks. Also, can you say what "didn't work"
means? In other words, exactly how did the result of this query differ
from what you wanted.

 

[Guest]

SELECT Statistics.[Organization Name], Statistics.Type, Statistics.EA,
Statistics.IEE, Statistics.Benchmarked, Statistics.[IR Completed],
Statistics.[TEA Approved]
FROM Statistics
GROUP BY Statistics.[Organization Name], Statistics.Type, Statistics.EA,
Statistics.IEE, Statistics.Benchmarked, Statistics.[IR Completed],
Statistics.[TEA Approved]
HAVING (((Statistics.EA) Between [Start Date:] And [End Date:]))
ORDER BY Statistics.[Organization Name];

Steve.

What I meant by "it didn't work" was that when I put the same parameter for
each of the four columns on different lines none of dates showed in the
datasheet view.

 

[Steve Schapel]

Wade,

Thanks. What I meant was the SQL of the one that didn't work
Anyway, I can't see why this is a Totals Query. There should be no
Group By clause, as there is no aggregate function.

I am not sure which are the 4 date columns you mentioned. I will assume
it is EA, IEE, IR Completed, and TEA Approved. In this case, the SQL
should be like this...

SELECT Statistics.[Organization Name], Statistics.Type, Statistics.EA,
Statistics.IEE, Statistics.Benchmarked, Statistics.[IR Completed],
Statistics.[TEA Approved]
FROM Statistics
WHERE (((Statistics.EA) Between [Start Date:] And [End Date:]) Or
((Statistics.IEE) Between [Start Date:] And [End Date:]) Or
((Statistics.[IR Completed]) Between [Start Date:] And [End Date:]) Or
((Statistics.[TEA Approved]) Between [Start Date:] And [End Date:]))
ORDER BY Statistics.[Organization Name]

Give that a go, and let us know how you get on.