Results 1 to 5 of 5
  1. #1
    TheSpecialist is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Georgia
    Posts
    47

    Sql I just want to be able to pull the number instead of the date

    Good evening,

    The script below pulls the by the date however, I would like to enter a number as my parameter instead of by date. I like how my script is working below I just want to change it to be able to pull the number. I would like to enter the number from my database and it pulls everytime that number came and shows the dates of all the time it came with 5 days before and 5 days after. So if I put in 333 in as my parameter and the date this number fell on was 2/1/2012. It will show. Along with the numbers that fell after , for instance 333 and I would like to be able to see what fell on 2/2/2012 and so on.

    PARAMETERS [Enter Date:] DateTime;


    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL
    SELECT "Cash4Table", YourDate, YourNumber
    FROM Cash4Table
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL
    SELECT "GAFiveTable", YourDate, YourNumber
    FROM GAFiveTable
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL SELECT "Fantasy5Table", YourDate, YourNumber
    FROM Fantasy5Table
    WHERE YourDate BETWEEN [Enter Date:]-4 AND [Enter Date:]+5
    ORDER BY SourceTable, YourDate;


    Thank you in advance

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    --This is untested --
    Code:
    PARAMETERS [Enter Date:] DateTime, [Enter YourNumber:] Long;
    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5 AND YourNumber = TheNumber 
    ORDER BY SourceTable, YourDate;
    
    UNION ALL
    SELECT "Cash4Table", YourDate, YourNumber
    FROM Cash4Table
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5 AND YourNumber = TheNumber 
    UNION ALL
    SELECT "GAFiveTable", YourDate, YourNumber
    FROM GAFiveTable
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5 AND YourNumber = TheNumber 
    UNION ALL SELECT "Fantasy5Table", YourDate, YourNumber
    FROM Fantasy5Table
    WHERE YourDate BETWEEN [Enter Date:]-4 AND [Enter Date:]+5 AND YourNumber = TheNumber
    From HELP:
    In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement.


    I haven't designed a query using the "Parameters" keyword - I always use a form to get the parameters for the query.

  3. #3
    TheSpecialist is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Georgia
    Posts
    47
    Thank you I am testing it now

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was finally able to get the query to execute, but it took a workaround - two queries.

    The Union query named "Query1Sub"
    Code:
    PARAMETERS [Enter Date:] DateTime, [Enter TheNumber:] Long;
    SELECT "YourTable" AS SourceTable, YourTable.YourDate, YourTable.YourNumber
    FROM YourTable
    WHERE (((YourTable.[YourDate]) Between [Enter Date:]-5 And [Enter Date:]+5))
    UNION ALL 
    SELECT "Cash4Table", YourDate, YourNumber
    FROM Cash4Table
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5;
    UNION ALL
    SELECT "GAFiveTable", YourDate, YourNumber
    FROM GAFiveTable
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL SELECT "Fantasy5Table", YourDate, YourNumber
    FROM Fantasy5Table
    WHERE YourDate BETWEEN [Enter Date:]-4 AND [Enter Date:]+5;
    And main query named "Query1Main"
    Code:
    SELECT SourceTable, YourDate, YourNumber
    FROM Query1Sub
    ORDER BY SourceTable, YourDate;
    Open "Query1Main", enter the parameters.......

  5. #5
    TheSpecialist is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Georgia
    Posts
    47
    Thank you so much

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-26-2016, 01:37 PM
  2. Replies: 2
    Last Post: 10-30-2015, 12:19 PM
  3. Need to pull number from within text string
    By FrankBone in forum Queries
    Replies: 8
    Last Post: 06-09-2015, 09:44 AM
  4. How to pull first Code after a certain date
    By MiroY72 in forum Queries
    Replies: 1
    Last Post: 04-22-2013, 01:09 PM
  5. Replies: 15
    Last Post: 04-06-2012, 10:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums