none
Filter Problem RRS feed

  • Question

  • I have a search form that when you can select multiple filters to get down to a specific list of records. From there you can then open a form for just the record you want to look at. When this happens, it passes three values from the subsearchform to the form it needs to open. I recently had to change one of the filter opens from text field called period to a completed date field formatted as a date. I believe my error is because of the change from text to date but I am not sure how to alter it.

    My current code is:


    Private Sub Form_DblClick(Cancel As Integer)

    DoCmd.OpenForm FormName:="frmActivityProjects", _
          WhereCondition:="Activity_ID = """ & Me.Activity_ID & """ " & _
          "AND OrgID = """ & Me.OrgID & """" & _
          "AND CompletionDate = """ & Me.CompletionDate & """"
    End Sub

    The error is on the CompletionDate field - any suggestions how to change it?

    Any help is appreciated!

    Tuesday, December 19, 2017 4:54 PM

Answers

  • DoCmd.OpenForm FormName:="frmActivityProjects", _
          WhereCondition:="Activity_ID = """ & Me.Activity_ID & """ " & _
          "AND OrgID = """ & Me.OrgID & """" & _
          "AND CompletionDate = """ & Me.CompletionDate & """"
    End Sub

    The error is on the CompletionDate field - any suggestions how to change it?

    Hi seebert,

    Dates should be enclosed by "#" instead of quotes, so:  " AND CompletionDate = #" & Me.CompletionDate & "#".

    Take care that the CompletionDate is in American (mm-dd-yyyy) of ISO (yyyy-mm-dd) format.

    Imb.

    • Marked as answer by seebert Tuesday, December 19, 2017 6:02 PM
    Tuesday, December 19, 2017 5:21 PM

All replies

  • DoCmd.OpenForm FormName:="frmActivityProjects", _
          WhereCondition:="Activity_ID = """ & Me.Activity_ID & """ " & _
          "AND OrgID = """ & Me.OrgID & """" & _
          "AND CompletionDate = """ & Me.CompletionDate & """"
    End Sub

    The error is on the CompletionDate field - any suggestions how to change it?

    Hi seebert,

    Dates should be enclosed by "#" instead of quotes, so:  " AND CompletionDate = #" & Me.CompletionDate & "#".

    Take care that the CompletionDate is in American (mm-dd-yyyy) of ISO (yyyy-mm-dd) format.

    Imb.

    • Marked as answer by seebert Tuesday, December 19, 2017 6:02 PM
    Tuesday, December 19, 2017 5:21 PM
  • That was exactly what I needed.

    Thanks!!

    Tuesday, December 19, 2017 6:02 PM