Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: This is more of an Access question, but I thought I'd try..

    I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.

    I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:

    DECLARE @sDate AS DATETIME
    DECLARE @eDate AS DATETIME

    SET @sDate = 'this string gets constructed during the On Open event of a report
    SET @eDate = 'same thing here

    SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate)
    FROM somewhere
    WHERE somefield BETWEEN @sDate AND @eDate

    I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.

    The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.

    Thoughts?

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I assume you are using linked sql tables in your access database....

    Have you tried using a pass through query? I haven't played with access in a long time, but from memory setting the query type to pass through will stop access from checking the syntax against it's internal standard and will just throw it at what engine you have linked to.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by rokslide
    I assume you are using linked sql tables in your access database....

    Have you tried using a pass through query? I haven't played with access in a long time, but from memory setting the query type to pass through will stop access from checking the syntax against it's internal standard and will just throw it at what engine you have linked to.
    Essentially they are linked tables, but with a bit more control. Linked tables tend to be a bit limited, .adp (access projects) are specifically designed to be a front end for sql server 2000.

    As far as the query type goes, this would be the RecordSource property for a report, therefore you can't really choose what kind of query it is. I'm a bit stumped.

  4. #4
    Join Date
    Feb 2004
    Posts
    7

    here 'tis

    Hopefully this will help you out...

    1. Create a stored procedure with your date variables appropriately declared;
    2. Make the SP the recordsource of the report;
    3. In the form properties box, go to Input Parameters;
    4. Set your SP variables in this property (e.g. @StartDate = StartDate())
    *note, here, I've referred to a function that can be set with a Global
    Variable in Access;
    5. Save your report...

    Generally, when creating access reports, you'll have a command button that opens the report. In the ONCLICK event of this button, set your global variable for the date, then open the report. WHen the report opens, your input parameter will refer to the function that's set by the global.

Posting Permissions

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