Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    You can use the criteria...

    Between [Enter Start Date:] And [Enter End Date:]

    This will prompt them for both dates, and report only the data between them..

    Is this what you're looking for? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    I don't know where to put the Start Date and End Date variables.

    The criteria for the date column is currently hard coded. Are you suggesting replacing the hard-coded date range with the Start/end date vars (prompted for when the report runs)?

    Thanks for helping...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    How is the criteria for the date hard-coded?

    In your query design, just below the field designation, there's an area for criteria. if you place the above in that field, it'll prompt them for the criteria...

    You might give it a try, if you're asking for a programattical solution, you're more of a wizard at that than I... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    I got'cha. I tried it and it works fine. Just need to prompt with the default dates so user can press [Enter] to accept or type a new date...

    I like that criteria syntax...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    Hmmm....

    So when you type "Between [Enter Start Date:] And [Enter End Date:]" you want your hard-coded defaults to be in the prompts?

    Hmmm... <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>!!!

    If you put your hard-coded date in the criteria field just below that, like this: #6/28/01# It will use that if nothing is entered in the box...

    Does that work for you?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    GifMeister,

    Yes! Thanks. That's 'zactly what I need. And saves me lots of time trying to figure out how to do this via code. Perfect.

    p.s. While I'm thinking about it: how about a smilie that shows a guy "dying of laughter" -- crying, pounding his fists with a big smile...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Modify Query (97 SR2)

    Hi Kevin,
    One small problem with that is that if your users do enter a date range then the query will return info that matches that range or your default range - i.e. you'll always get the data that matches your defaults plus any that matches whatever they enter.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    Uh oh. That's not going to work then. I'm going to try posting this topic to the VBA forum an see if someone can help me get ahold of the query object to modify it.

    Thanks for all your help.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Modify Query (97 SR2)

    Hi Kevin,
    Sorry - I was in a bit of a rush when I posted that. I should have added a solution really! If you run the report from a form, you can add two text boxes to the form for your criteria (with default values equal to your required defaults) and then add =Forms!formnamehere!textboxnamehere as the criteria in your query.
    If you can't run it from a form, repost here and we can come up with some code to do it.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    Rory, Drk,

    I think I'll leave off the default values for now and consider this one resolved. Thanks to you both for your help! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    p.s. Rory, did I ever tell you, you look just like my old guitar and voice teacher?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    50,000,000 Rory fans can't be wrong!

    Kevin, I know that you're considering this resolved, but I'm just getting round to checking the threads from the past couple of days. Here's my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>: the source for the report is a query, which is just a SQL statement. It sounds like the SELECT and FROM clauses of your SQL statement will always be the same, and the WHERE clause should default to your pre-specified criteria, but if the user wants to have other criteria, the WHERE clause should change accordingly. Sooo, perhaps when the report is launched, you ask the user to specify a date range, or just press <Enter>. Behind the scenes, you build up your SQL statement in VBA setting the WHERE clause to your default if the user didn't enter something, or to their date range if they did. Then you can set the record source of the report to that SQL statement.

    Sound like something that you'd be interested in?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    Shane,

    >>Sound like something that you'd be interested in?

    Quite frankly, YES! That's the idea I was hoping to explore but I don't know the Access objects well enough to modify the query's where clause. And since Rory's "criteria prompts" worked just fine, I figured it best to drop the subject. But...

    If you wouldn't mind pointing me in the right direction I would be unhesitatingly grateful. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    Lemme see what I an gin up by way of demo, and I'll post back.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  15. #15
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Query (97 SR2)

    See if this does the trick for you. You might consider a different mechanism for capturing user input, and you'd certainly want more robust error handling <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>, but this should give you a push in the right direction for dynamically generating SQL statements to use as record sources for your reports.

    I backsaved to Access 97, but haven't had a chance to road test it in that version; works in Access 2000, SR2. Both versions are in the zip. HTH! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Page 1 of 2 12 LastLast

Posting Permissions

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