Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Smile Unanswered: DATE Range in Union All Query

    I have a UNION ALL query.

    It requires a >=beginning and <=ending date for each of the three queries that make up the union all query. ie. >=#4/1/2011# AND <= #4/30/2011#

    Each of the ">= date" are exactly the same, as are the "<= date".

    They are separate named date fields in the same table.

    Each month I must open the SQL UNION ALL query and update the nine dates in each of the three queries that make up the union query. To much opportunity for a user mistake.

    Is there a way when executing the UNION ALL query to enter the >=date AND <=date in all three of the queries automatically as the query runs?

    I actually have four UNION ALL queries that do this which equals 24 date entries. Yikes!!

    H. E. L. P.

    thanks

    Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    edit your SQL (one last time) and change this --

    WHERE somedate >= #4/1/2011# AND somedate <= #4/30/2011#

    to this --

    WHERE somedate >= [ enter start date ] AND somedate <= [ enter end date ]

    use only those two phrases, exactly the same, with the square brackets

    they will prompt you, one after the other, when the query runs

    make sure you enter the octothorps when giving the values

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I know you would be the one here first. thanks Rudy.

    Well with your scenario I would still have to enter the data in the prompts nine time per each UNION ALL QUERY.

    That's what I'm trying to avoid.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Well with your scenario I would still have to enter the data in the prompts nine time per each UNION ALL QUERY.
    may i suggest that you actually test it, rather than make an assumption

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    You know . . . after I sent that the hair on the back of my neck stood up as I though that maybe there is something in what you said that I missed!

    I'll try that tomorrow when I'm in the office!

    Octothorps ? [ ]

    Wait a second . . . somedate? There are three different date fields.

    How does somedate distinguish listingdate, pendingdate, closeddate?

    Maybe is does understand that because it's a union all query..

    I'll investigate.

    Thanks Rudy . . .

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Octothorps ? [ ]
    hash marks




    Quote Originally Posted by Rick Schreiber View Post
    How does somedate distinguish listingdate, pendingdate, closeddate?
    somedate was just the name i used because you never mentioned what the date columns were
    Code:
    SELECT ...
      FROM ...
     WHERE listingdate >= [ enter start date ] AND listingdate <= [ enter end date ]
    UNION ALL
    SELECT ...
      FROM ...
     WHERE pendingdate >= [ enter start date ] AND pendingdate <= [ enter end date ]
    UNION ALL
    SELECT ...
      FROM ...
     WHERE closeddate >= [ enter start date ] AND closeddate <= [ enter end date ]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Pure Genious Rudy!

    It works flawlessly!

    Thanks.

    Rick

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    "syntax error mission operator"

    This one is coded a little differently - it doesn't have all of the curly brackets, etc.

    I've entered the changes to no avail.

    Code:
    SELECT 'NewListings' AS rowtype
         , COUNT(*) AS amount
      FROM tblCARETSData
     WHERE ListingEntryDate BETWEEN >= [ enter START date ] 
                           AND ListingEntryDate <= [ enter END date ]
       AND City IN ('bd','cthc','coa','dctr','dhs'
                   ,'ds','iwld','iw','ind','lq'
                   ,'mec','mntc','pdst','pspr','rm'
                   ,'salt','sltb','th','tp');
    Rick

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    Code:
     WHERE ListingEntryDate BETWEEN >= [ enter START date ] 
                           AND ListingEntryDate <= [ enter END date ]
    that stuff in red, include it out

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Removed >= and <=

    The [ enter END date ] is highlighted as a syntax error ?

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Got IT!

    I kept at it and found the solution.


    Code:
    SELECT 'NewListings' AS rowtype
         , COUNT(*) AS amount
      FROM tblCARETSData
     WHERE [ListingEntryDate] >= [ enter START date ]
    AND  [ListingEntryDate]  <=[ enter END date ]
       AND City IN ('bd','cthc','coa','dctr','dhs'
                   ,'ds','iwld','iw','ind','lq'
                   ,'mec','mntc','pdst','pspr','rm'
                   ,'salt','sltb','th','tp')
    Your right - it really has to be EXACT, including the brackets for the field names.

    I carried this for the other fields and it computes correctly.

    Thanks Rudy.

  12. #12
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Hay Rick when I getting the query asking a question I always put a ?? In the [] so I know that query is asking the question not asking for a feildname value.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by myle View Post
    ... I always put a ?? In the []
    good idea

    i use the word "enter" for the same purpose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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