Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting the Same Parameter for Several Queries (Ac

    There is a set of identical Queries that we run monthly on a number of SQL Server databases. These are migrated from Access to Excel - using the Transfer Spreadsheet Method - for report compilation.

    All Queries have the same parameter: [Report Date].

    There seems to be a case of <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> in identifying how to enter the [Report Date] parameter once and once only.

    Any ideas?

    P.S. (Trying to use DAO from Excel produces unsound data. Perhaps the SQL is too complex.)
    Gre

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

    Re: Setting the Same Parameter for Several Queries (Ac

    Create a form with an unbound text box for the Report Date. Set its Format property to Medium Date or something like that. Let's say you name the form frmDateInput and the text box txtReportDate.

    Set the parameter of all queries to [Forms]![frmDateInput]![txtReportDate]

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting the Same Parameter for Several Queries

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Gre

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Setting the Same Parameter for Several Queries (Ac

    Another alternative is to set up a global variable in a module, call it ReportDate if you want. Also in a module, create a public function called getReportDate, as follows:

    Public Function getReportDate as Date
    getReportDate = ReportDate
    End Function

    There are advantages and disadvantages to this method, as there are to using the form method that Hans suggested. The global variable method doesn't require that the form be opened, although it does assume that you have already initialized ReportDate somewhere. (Note: the above function doesn't contain any error code in case ReportDate hasn't been initialized, add that yourself). Generally, I opt for the form method myself, but sometimes the global variable method works better for a specific situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting the Same Parameter for Several Queries (Ac

    The global variable option has been posted already. Just an FYI, I have a form based calendar (MiniCalendar) which prompts the user for a date from a calendar. It stores that date as a global variable, and you can 'retrieve' that variable through a function. My site is currently 'being built', but there is a relatively up to date version on the Access Web. (http://www.mvps.org/Access) (Search for MiniCalendar).

Posting Permissions

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