Problems with running a query within VBA

[PatrickJohnson]

i have the following code which will basically has the user enter a start and end date, then runs a query using the start date as a criteria, then adds 1 to the start date and does it again, etc., until the end date is reached.

Function Float_Macro()
On Error GoTo Float_Macro_Err
Dim dateStartDate As Date
Dim dateEndDate As Date

DoCmd.SetWarnings False 'Turn off warnings
DoCmd.OpenQuery "GET Float Check Delete", acViewNormal, acEdit 'Clear Floats Table
dateStartDate = InputBox("Enter Sample Start Date", "Start Date", Date) 'User Input Box: Start Date
dateEndDate = InputBox("Enter Sample End Date", "End Date", dateStartDate + 365) 'User Input Box: End Date
Do While dateStartDate < dateEndDate 'Sets cutoff point for loop
SendKeys dateStartDate, False 'Puts user entered data for Start Date into Query Input Box
SendKeys "{ENTER}", False 'Chooses "OK" on Query Input Box
DoCmd.OpenQuery "GET Float Check Append", acViewNormal, acEdit 'adds data to list
dateStartDate = dateStartDate + 1 'Sets next sample date

Loop
DoCmd.OpenQuery "GET Float Time Card Entries Table", acViewNormal, acEdit 'Averages Float Data

Float_Macro_Exit:
Exit Function

Float_Macro_Err:
MsgBox Error$
Resume Float_Macro_Exit

End Function

The problem is the only way I've been able to make it work is the above, where a dialog pops up initally where the user enters start date, then another where they enter end date, then each time the query is run which has the report date as a user entered field, and i use the very inefficient SendKeys to populate the field when prompted and continue on.

My desired end result would be to have the user enter a start and end date, then run the query for every date including and between, appending each query result set to the previous in a table, then display the final table.

How can i make it step through and run the query using the input box date as a starting point but then not popping up the dialog box anymore?

 

[MarkK]

Put the query text in the code, modify the parameters and the run the query. Code might look something like...

dim dStart as date
dim dEnd as date

dStart = InputBox("...
dEnd = InputBox("...

CurrentDb.Execute _
  "DELETE FROM tTable " & _
  "WHERE Date >= #" & dStart & "# AND Date <= #" & dEnd & "#"

See? You can write the query in the design grid but then copy the SQL text into your code as a string. Then you can modify the parameters of your queries on the fly using data from the user.
Cheers,