Results 1 to 2 of 2
2005-07-16, 09:16 #1
- Join Date
- Feb 2001
- Thanked 2 Times in 2 Posts
Date range parameters (Access 2000)
I have a crosstab query where the rows are employee names and the columns represent a dynamic set of 15 dates selected by the criterion >=Date() And <=Date()+15. A count function is used to set the value to "1" if an employee is available on a date.
Is it possible to add a pair or parameters so that the user can specify a Start Date and an End Date, and select only records where (a) the employee is available on at least one date in the specified range (inclusive of the start and end dates)?
If so, is it similarly possible to specify a selection ([img]/forums/images/smilies/cool.gif[/img] where the employee is available on a percentage of the dates within the specified range? © where the employee is available on the first OR the second date within the specified range? (d) on the last AND the next-to-last date within the specified range?
2005-07-16, 14:58 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Date range parameters (Access 2000)
This is one of three questions you've posted about parameters in a query. Are these all the same query, or what? This seems to be incredibly and increasingly complicated. You might be better served by explaining what you're trying to accomplish with all these parameter queries because someone may be able to suggest an alternate and simpler approach. If these are continuation questions to another thread, you either should post them in the original thread or post a link to the original in your new post so those who might help don't have to ask all the same questions over again. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
I'm not sure I even understand what you are asking for in this post. Is the cross tab based on a table or on another query? Are you doing this for a report or are you trying to display the query dynamically? Parameters in a cross tab can't just be added to the grid in the criteria line for a report, you must add them to query parameters as well. The same holds true if the cross tab is based on another query: the parameters have to be set as a query parameter in the source query. If the user is interacting with the query, it's different, but still a good idea to specifically add the parameters to the query parameters, not just the criteria line.
You already have start and end date parameters in >=Date() And <=Date()+15. Are you asking about replacing those or using alternate parameters? You certainly can't have both because they will almost certainly conflict. The only practical way to handle this kind of complexity is by presenting the data in a form and building the SQL for the query in code behind the form. That would allow you to have alternate parameters, filter the results, etc.Charlotte