Share via

Why can't I use a variable to pass a date to this sub to set parameter values?

Anonymous
2025-05-23T12:20:01+00:00

Hello. I'm using the sub from this thread https://answers.microsoft.com/en-us/msoffice/forum/all/can-you-use-a-public-function-sub-to-set-a/1043226e-ae37-450a-a045-3261256a9a29  to set a forms recordset. It works fine on the first form I set up. I couldn't get it to work with a second. After a LOT of pulling my hair out trying to figure out why it wouldn't work, I finally found the culprit. It seems like it doesn't like me sending dates to it via variable. I've tried all the combos below. For lines 820, the bottom 3 work perfectly. The top 3 don't return anything. I've tried the top 3 with the various lines 790 and 800. NONE work. Any advice why? (For testing purposes I just comment what I tried last, and uncomment what I'm trying this go around.)

          Dim sLocation As String, sCO As String

          Dim iEquipType As Integer, iExamFreq As Integer

          Dim dExamDateStart As Date, dExamDateEnd As Date

          Dim sExamDateStart As String, sExamDateEnd As String

'790       dExamDateStart = #5/1/2025#

'790       dExamDateStart = Format(#5/1/2025#, "mm/dd/yyyy")

790       sExamDateStart = "5/1/2025"

'800       dExamDateEnd = #5/23/2025#

'800       dExamDateEnd = Format(#5/23/2025#, "mm/dd/yyyy")

800       sExamDateEnd = "5/1/2025"

810       sCO = Nz(Me.Parent!txtCOFilter.Value, "")

'820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", sLocation, "parEquipmentTypeID", iEquipType, "parExamFrequencyID", iExamFreq, "parExamDateStart", dExamDateStart, "parExamDateEnd", dExamDateStart, "parCO#", sCO)

'820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", sLocation, "parEquipmentTypeID", iEquipType, "parExamFrequencyID", iExamFreq, "parExamDateStart", Format(dExamDateStart, "mm/dd/yyyy"), "parExamDateEnd", Format(dExamDateStart, "mm/dd/yyyy"), "parCO#", sCO)

820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", sLocation, "parEquipmentTypeID", iEquipType, "parExamFrequencyID", iExamFreq, "parExamDateStart", Format(sExamDateStart, "mm/dd/yyyy"), "parExamDateEnd", Format(sExamDateStart, "mm/dd/yyyy"), "parCO#", sCO)

'820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", "", "parEquipmentTypeID", 0, "parExamFrequencyID", 0, "parExamDateStart", #5/1/2025#, "parExamDateEnd", #5/23/2025#, "parCO#", "")

'820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", "", "parEquipmentTypeID", 0, "parExamFrequencyID", 0, "parExamDateStart", Format(#5/1/2025#, "mm/dd/yyyy"), "parExamDateEnd", Format(#5/23/2025#, "mm/dd/yyyy"), "parCO#", "")

'820       Call SetRecordset(Me, "qryRecentExams_Primary", "parEquipmentLocation", sLocation, "parEquipmentTypeID", iEquipType, "parExamFrequencyID", iExamFreq, "parExamDateStart", #5/1/2025#, "parExamDateEnd", #5/23/2025#, "parCO#", sCO)
Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 11,381 Reputation points Volunteer Moderator
    2025-05-23T19:34:06+00:00

    Standard US settings.

    Are you able to share a sample db?

    0 comments
  2. Anonymous
    2025-05-23T20:35:21+00:00

    So, you're saying I should be using the SQL and not the saved queries for these forms? That seems annoying if I'm using the same query in multiple forms and have to make an edit. But I guess there's the argument that I probably won't be changing these very much, if ever, once I get it all working...

    If you have a query which can be used as the RecordSource property for multiple objects then you can build and pass the SQL statement to the objects as its OpenArgs property. The following is a simple example which does this with a report, building the SQL statement on the basis of values entered in controls in a dialogue form:

    Private Sub cmdOpenReport_Click() 

    Dim strSQL As String  
Dim strWhere As String  
  
strSQL = "SELECT Employees.\*, FirstName & "" "" & Lastname AS FullName, " & \_  
    "Projects.Project FROM Projects " & \_  
    "RIGHT JOIN (Employees LEFT JOIN ProjectEmployees " & \_  
    "ON Employees.EmployeeID=ProjectEmployees.EmployeeID) ON " & \_  
    "Projects.ProjectID=ProjectEmployees.ProjectID WHERE True"  
  
If Not IsNull(Me.cboDepartment) Then  
    strWhere = strWhere & " And Department = """ & Me.cboDepartment & """"  
End If  
  
If Not IsNull(Me.cboCity) Then  
    strWhere = strWhere & " And City = """ & Me.cboCity & """"  
End If  
      
If Not IsNull(Me.txtDateFrom) Then  
    strWhere = strWhere & " And DateAppointed >= #" & \_  
        Format(Me.txtDateFrom, "yyyy-mm-dd") & "#"  
End If  
 
If Not IsNull(Me.txDateTo) Then  
    strWhere = strWhere & " And DateAppointed < #" & \_  
        Format(Me.txDateTo, "yyyy-mm-dd") & "#+1"  
End If  
  
strSQL = strSQL & strWhere & ";"  
  
DoCmd.OpenReport "rptEmployees", View:=acViewPreview, OpenArgs:=strSQL

    End Sub

    In this case the code can open only one report, but if it were to be split so that the SQL statement is built in a single Public function in a standard module, then the function could be called in multiple other functions or procedures each of which opens a different form or report.

    In each form or report's Open event procedure the SQL statement is assigned to the RecordSource property as follows:

    Private Sub Report_Open(Cancel As Integer) 

     If Not IsNull(Me.OpenArgs) Then  
    Me.RecordSource = Me.OpenArgs  
End If

    End Sub

    If you need to edit the SQL statement you'd do it only once in the function.

    As regards 'I have no grasp of how I'd assign values to something that may or may not exist, and I don't give the name of it if it does exist', you do not assign the value to anything. If a query includes one or more parameters, when those parameters are evaluated by looping through the query's Parameters collection. Each parameter gets its value from the object which is referenced as the parameter's Name property. It's a pull rather than a push operation.

    0 comments