Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts