Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    1

    Unanswered: sql please help me please

    The SQl script below. It currently runs off me entering the date. However, I now want to be able to enter a number in the parameter box instead. For instance, If I enter 123 I want to see all the times 123 came along with what came 2 days before and 1 day after.

    Example: 2/1/2016 ----123
    1/31/2016----234
    1/30/2016----456
    2/2/2016-----555


    This is my script below.

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

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Q1 = select * from table where [dateFld] between DateAdd("d",-1,[Enter Date]) and between DateAdd("d",+2,[Enter Date])

    then get Max & Min of dates from Q1
    d1 = dMax("[DateFld]","Q1")
    d2 = dmin("[DateFld]","Q1")

    then pull date from tbl
    Q2 = select * from table where [DateFld] between #" & d1 & "# and #" & d2 & "#"

  3. #3
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Try with the following change:
    Code:
    PARAMETERS [Enter Date:] DateTime, [Enter YourNumber:] IEEEDouble;
    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE (YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1) AND (YourNumber = [Enter YourNumber])
    UNION ALL
    SELECT "Cash4Table", YourDate, YourNumber
    FROM Cash4Table
    WHERE (YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1) AND (YourNumber = [Enter YourNumber])
    UNION ALL
    SELECT "Fantasy5Table", yourDate, YourNumber
    FROM Fantasy5Table
    WHERE (YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1) AND (YourNumber = [Enter YourNumber])
    UNION ALL
    SELECT "GAFiveTable", YourDate, YourNumber
    FROM GAFiveTable
    WHERE (YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1) AND (YourNumber = [Enter YourNumber])
    UNION ALL SELECT "MoonPhaseTable", YourDate, YourNumber
    FROM MoonPhaseTable
    WHERE (YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1) AND (YourNumber = [Enter YourNumber])
    ORDER BY SourceTable, YourDate;
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

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