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