Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    parameter query fakeout (Access 2000 SP2)

    I was able in the VB code, to get parameters to store from a form and so now I have a nice SQL statement that works in SQL query analyzer. I'd like to turn this into a view, so I can call it from a stored procedure, but I can't figure out how to do it. Here's my code:

    Me.ServerFilter = "(BIGChecks.bg_CHK_NO)>='" & Me.chksdate & "' And (BIGChecks.bg_CHK_NO)<='" & Me.chkedate & "'"
    strFilter = Me.ServerFilter
    strSelect = "SELECT BIGChecks.bg_CHK_NO, BIGChecks.bg_CHK_DAT, BIGChecks.AMOUNT "
    strfrom = "FROM BIGChecks "
    strGroup = "GROUP BY BIGChecks.bg_CHK_NO, BIGChecks.bg_CHK_DAT, BIGChecks.AMOUNT "
    strhaving = "HAVING " & strFilter
    strsql = strSelect & strfrom & strGroup & strhaving & ";"

    I get strsql to be:
    SELECT BIGChecks.bg_CHK_NO, BIGChecks.bg_CHK_DAT, BIGChecks.AMOUNT FROM BIGChecks GROUP BY BIGChecks.bg_CHK_NO, BIGChecks.bg_CHK_DAT, BIGChecks.AMOUNT HAVING (BIGChecks.bg_CHK_NO)>='00046780' And (BIGChecks.bg_CHK_NO)<='00046782';

    So is there a way to turn this strsql into a view? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Carpy Diem, it&#39;s .

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

    Re: parameter query fakeout (Access 2000 SP2)

    Sorry about the lack of replies. Apparently not many Loungers are working with SQL Server.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: parameter query fakeout (Access 2000 SP2)

    Sorry Peggy - I missed you post. In order to do this sort of thing in an MDB file, your best choice is to use a pass through query that you build and save dynamically, so that isn't really using a view. You can do this sort of thing in a stored procedure, but views can't have parameters that are dynamic. But aren't you using ADP files? If that's the case you should be able to pass parameters to a stored procedure and have it return data. Am I missing something here?
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: parameter query fakeout (Access 2000 SP2)

    Wendell and Hans,

    After banging my head upon my desk a few times, I finally decided to review the data that's being exported and found that I could simply things much better to set up a check table with an "X" field for exported, and then load the new day's bigcheck data into this table. Then, by utilizing a DTS, I'm able to totally automate the process and have the users run the DTS, which gets the data, mooshes it around and exports it into a format that they can then send to the bank.

    Oh, the learning curve I'm having here... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Carpy Diem, it&#39;s .

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: parameter query fakeout (Access 2000 SP2)

    I understand that learning curve issue - I hadn't done any significant SQL Server work for a few months, and now I'm back in it and having to be retrained! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    Wendell

Posting Permissions

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