Results 1 to 2 of 2
  1. #1
    Hobbes29 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Posts
    10

    Date Increment from User defined start date in a Form

    I am looking for assistance in using SQL and a form.

    I have a data sheet in Access 2007 that has a number of Columns. Three of the column names are Name (string), myHour (number), and Date_Local (date)

    What I am trying to do is have the user enter a name, a start date, a number between 1-24 and number of days to increment between 1-14 in a form. As an example, the user enters the following criteria in the form:
    name in textbox1: Bob
    Hour in textbox2: 4
    Start date in textbox3: 1/25/2010
    # of days to increment by in textbox4: 7

    However, when I enter the above information into the form the query executes but the start date begins at 1/1/2010 instead of 1/25/2010.

    I've looked at a number of ways to try and get the results I need and found DateAdd() to be the best option, but since my query isn't working, I think it has to do with the way I have my sql written.

    Function:


    DateAdd( Interval, Number, date)

    Clean Function
    DateAdd("d",incDate,startDate)

    Function with form informatoin
    ((DateAdd("d",[Forms]![QBF_Form]![incDate],(([Table1].[DATE_LOCAL])=[Forms]![QBF_Form]![startDate]))))

    Parameters passed from the form:
    WhatAI (textbox1) = The name I want the user to query the results (Bob)
    WhatHour (textbox2)= The number of hour (4)
    startDate (textbox3)= The date to start the query from in all data (1/25/2010)
    incDate ((textbox1))= the number of days to increment to look for matching data (7)

    The final resulting output is all records for Bob, during hour 4, starting on 1/25/2010 (until EOF) every seven days.

    Can someone offer some insight or direction to make the sql work? I can provide more information if needed.

    SELECT Table1.DATE_LOCAL, Table1.AI_NAME
    FROM Table1
    WHERE (((Table1.HOUR_LOCAL)=[Forms]![QBF_Form]![WhatHour]) AND ((Table1.AI_NAME)=[Forms]![QBF_Form]![WhatAI]) AND ((DateAdd("d",[Forms]![QBF_Form]![incDate],(([Table1].[DATE_LOCAL])=[Forms]![QBF_Form]![startDate]))) AND Table1.DATE_LOCAL = (([Forms]![QBF_Form]![startDate]))
    ORDER BY Table1.DATE_LOCAL;

    Thanks,
    Ken

  2. #2
    Hobbes29 is offline Novice
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Posts
    10

    Date Increment from User defined start date in a Form

    I figured this out. Something simple unfortunately-- I used a <= in my sql.

    SELECT Table1.DATE_LOCAL, Table1.Field3
    FROM Table1
    WHERE Forms!QBF_Form!startDate <= Table1.Date_Local and (DateAdd("d",Forms!QBF_Form!incDate,((Table1.DATE_ LOCAL)=Forms!QBF_Form!startDate)) And ((Table1.HOUR_LOCAL)=Forms!QBF_Form!WhatHour) And ((Table1.AI_NAME)=Forms!QBF_Form!WhatAI))
    ORDER BY Table1.DATE_LOCAL;

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

Similar Threads

  1. set date field on form to date variable
    By laavista in forum Access
    Replies: 3
    Last Post: 06-28-2010, 03:03 AM
  2. user selected date query
    By jamin14 in forum Queries
    Replies: 3
    Last Post: 03-31-2010, 02:11 PM
  3. Error: "User-defined type not defined"
    By mastromb in forum Programming
    Replies: 10
    Last Post: 01-08-2010, 02:57 PM
  4. Help!!! user friendly forms/date entry
    By megank in forum Access
    Replies: 3
    Last Post: 03-31-2009, 09:47 AM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

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