SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Access: query criteria from tables??

    I have a several similar queries. Each month I have to change the date range criteria in each one to run my report (ie: >=03/01/04 and <=03/31/04).

    Doing this in each query takes too long, and leaves the door open for mistakes.

    Is there a way I can set my criteria in each query to tell it to go and read the values from other tables? Ideally, I'd like to see something like this for my criteria: " >=[startdatetable] and <=[enddatetable] " in all of my queries.

    Then, all I'd have to do is change the value in the table called "startdatetable" to 03/01/04, and the value in the "enddatetable" to 03/01/04, and that would cover it for all the queries.

    I am using Access 2000 on Win98 and am kind of new to Access (to this depth anyway).

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes you can do that

    or you can write the query to run for the month preceeding today's date, so you don't have to do anything, just run it any day in the following month to get the previous month's report

    or something like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes you can do that

    or you can write the query to run for the month preceeding today's date, so you don't have to do anything, just run it any day in the following month to get the previous month's report

    or something like that
    Oh wow! That would work better! Um.... how might I do that?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select foo
         , bar
         , datecol
      from yourtable 
     where datecol 
           between dateadd("m",-1
                 , dateadd("d",-day(date())+1,date())
                          )
               and dateadd("d",-day(date()),date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for the help, 937, but would you be so kind as to treat me like a total newbie?

    Where exactly do I put that code? I've only ever used the normal 'select query' grid to make queries. I assume this is supposed to go somewhere in the SQL view of the query?

    If so, exactly where?

    I'm sorry to make you spell it out for me...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when creating a query, go to View > SQL View

    paste it into the window, and run it

    it will then be in Design View if you go there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ctrlaltdel
    Is there a way I can set my criteria in each query to tell it to go and read the values from other tables? Ideally, I'd like to see something like this for my criteria: " >=[startdatetable] and <=[enddatetable] " in all of my queries.
    Just so that you know, you CAN use parameters in Access stored queries, simply put a "?" in each place, and then supply the values (in the same order) when you call the query, like this:
    Code:
    EXECUTE MyQuery #03/01/04#, #03/02/04#
    However, the solution that r937 presents is probably what you *actually* need.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by M@rco
    Just so that you know, you CAN use parameters in Access stored queries, simply put a "?" in each place, and then supply the values (in the same order) when you call the query
    You can also do it just like it was listed earlier and just use the names like that. When you do your call, it will replace those fields in the same order they're listed.

    I do it that way so I have a very readable query when I'm accessing the database directly, but yet it works through ado just fine.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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