Passing Parameters into a subquery

[davidvorob]

Hello,



I have a saved query that aggregates a field based on filtering out dates (where condition) and grouping by ID. The dates are entered by the user as parameter variables (UserStarDate and UserEndDate). I also need to code up a query that inserts the output of this query into my Target table, and occasionally needing to do it by ID that will be set in the VBA code. Is there a way to pass in the parameters into a query that looks something like "INESRT INTO targetTable FROM myQuery WHERE UserStartDate = #variableInVBA# AND UserEndDate = #variableInVBA#". I am able to usethe docmd.SetParameter if i'm doing a simple call of an already saved query, but not sure how to do it for a sub-query (the Insert query calls the original SELECT query). I don't necessarily want to save out the INSERT query as a standalone query as it will 1) dirty up the list of queries i have (already have too many) and 2) provide ability for someone to alter data by double clicking on it (would like to control it in a better way). Any help would be appreciated.

Thanks

[Gicu]

Have you lookup into using tempvars to store the two dates? Another way would be to use a couple of global variables for the two dates along with a couple of public functions in a standard module to retrieve them in your queries:

Option Database
Option Explicit

Public datUserStartDate as Date
Public datUserEndDate as Date

Public Function fnUserStartDate() as Date
    fnUserStartDate=datUserStartDate 
End Function

Public Function fnUserEndDate() as Date
   fnUserEndDate=datUserEndDate 
End Function

Cheers,

[Welshgasman]

You should not be exposing queries to users?

[kd2017]

I don't necessarily want to save out the INSERT query as a standalone query as it will 1) dirty up the list of queries i have (already have too many) and 2) provide ability for someone to alter data by double clicking on it (would like to control it in a better way).

Just hard code all of it?

Public Sub example()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim sql As String
    
    Dim startDate As Date
    Dim endDate As Date
    
    'search criterea
    startDate = #1/1/2021#
    endDate = #12/31/2021#
    
    'build the sql code
    sql = ""
    sql = sql & "INSERT INTO targetTable " & vbCrLf
    sql = sql & "SELECT field1, " & vbCrLf
    sql = sql & "       field2, " & vbCrLf
    sql = sql & "       field3 " & vbCrLf
    sql = sql & "FROM   sourceTable " & vbCrLf
    sql = sql & "WHERE  dateField >= #" & startDate & "#" & vbCrLf
    sql = sql & "       AND datefield <= #" & endDate & "#;"
    
    'print the sql string in the immediate window (Ctrl+G) make sure it looks correct
    Debug.Print sql
    
    'execute the sql code
    Set db = CurrentDb
    db.Execute sql, dbFailOnError
    
ExitHandler:
    'clean up objects
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub