how to prompt for input then populate SQL query

[karambos]

I have a file that I bring into Access. Then I execute an SQL query on it and output the results. I've got it working in the following manner:

Function Proc_daymacro()
On Error GoTo Proc_daymacro_Err
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "3days"
    DoCmd.TransferText acImportDelim, "3days Import Specification", "3days", "3days.csv", True, ""
    DoCmd.OpenQuery "All3", acViewNormal, acReadOnly
    DoCmd.TransferText acExportFixed, "3daysExportSpecification", "All3", "x3rd_day_17003.txt", True, ""
    DoCmd.SetWarnings True

Proc_daymacro_Exit:
    Exit Function
Proc_daymacro_Err:
    MsgBox Error$
    Resume Proc_daymacro_Exit
End Function

and the SQL query, All3, looks like this:

SELECT Serial
FROM 3days
WHERE date>=#12/2/2013#
GROUP BY Serial
HAVING count(*)=3;

Having got the above working, I would like to prompt the user for the date specified in the SQL query and dynamically insert it.

In An attempt to just get it working with a fixed date, this is the best I could come up with and it doesn't work:

Function Proc_daymacro2()
On Error GoTo Proc_daymacro_Err
   
      
    Dim strSQL As String
    strSQL = "SELECT Serial " & _
            "FROM 3days " & _
            "WHERE Date >= #12/2/2013# " & _
            "GROUP BY Serial " & _
            "HAVING count(*)=3"
       
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "3days"
    DoCmd.TransferText acImportDelim, "3days Import Specification", "3days", "3days.csv", True, ""
    DoCmd.RunSQL strSQL
    DoCmd.TransferText acExportFixed, "3daysExportSpecification", "All3", "x3rd_day_17003.txt", True, ""
    DoCmd.SetWarnings True

Proc_daymacro_Exit:
    Exit Function
Proc_daymacro_Err:
    MsgBox Error$
    Resume Proc_daymacro_Exit
End Function

I just found out that "DoCmd.RunSQL" can only be used if you are doing an INSERT, DELETE etc because it doesn't return a result. However, I cannot find out how to make it work.

I am grateful for any input

[June7]

Use a dynamic parameterized query object.

The parameter input can be a popup prompt or reference to a control on a form.

I never use popup prompt because can't validate user input.

The All3 query sql would be:

SELECT Serial FROM 3days WHERE Date >= [enter date value] GROUP BY Serial HAVING Count(*)=3

or

SELECT Serial FROM 3days WHERE Date >= [Forms]![formname]![controlname] GROUP BY Serial HAVING Count(*)=3

Then go back to the OpenQuery code.

[ssanfu]

"Date" is a poor name for a field because it is a reserved word in Access ("Date" is a command and a function). If you must use "Date", enclose it in brackets - [Date]..

"Help" states : filename - A string expression that's the full name, including the path, of the text file you want to import from, export to, or link to. You don't have a path.

If importing a CSV file multiple times AND the structure will always be the same, deleting the table, then recreating the table is inefficient.
It would be better to delete the records in the table"3days", then import the CSV file.

Creating a query (strSQL) in code allows you to use the recordset in code, usually by stepping through the record one record at a time.
To use the query you have now, you would need to create a saved query or creat a temporary query, then use code to modify the SQL of the saved query. You could then use the query in the DoCmd.TransferText command.

See Help "Query Def" for examples....


Edit: June beat me again.....