Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Default for parameter query (Access 2000)

    I have a parameter query for which the user needs to specify a future date range by entering a number of days from today. The following criterion does this OK, but is it possible to include a default value for the parameter, so that the user can just press the Enter key to specify the default value?

    Criterion: >=Date() And <=Date()+[Enter number of days:]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default for parameter query (Access 2000)

    The best way to do this is to create a form with a text box in which the user can enter the number of days. You can set the Default Value property of the text box to the desired number, for example 30. If you set the number format for the text box to General Number (or something like that) or 0. You can use the Before Update event of the text box to check that the number entered by the user is within a reasonable range (you don't want to accept 4567 or -30).

    Say that the form is named frmParameters and the text box txtNumberOfDays, then the criteria become

    Between Date() And Date()+[Forms]![frmParameters]![txtNumberOfDays]

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Default for parameter query (Access 2000)

    Thanks for your reply, Hans. I can't use a form because it's a crosstab query with dynamic column headings, and the programming needed is beyond my present skills so I need to use just the query for now. Is it possible to use Iif in a criterion somehow, with two alternatives, one using the default if the parameter is null, otherwise using the entered parameter?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default for parameter query (Access 2000)

    What I meant is that you can refer to a text box on a form in the criteria in the query.

    If you prefer not to use a form at all, set the criteria to

    Nz([Enter number of days:],30)

    where 30 is the default value.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Default for parameter query (Access 2000)

    That works fine thanks, Hans.

Posting Permissions

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