VBA Date will not be read. Date parameter as Date defined, still no progress!

[Susy]

Hi there,



after the issue with the date format was solved, I thought about creating a date range... but it is not working... below some snapshots of the code and error message...

my variable declaration for this case looks like this...

From Date: Dim strActfDate As String,
To Date: Dim strActtDate As String,
From and to: Dim strActftDate As String

If IsNull(Me.cboActionfDate) and isNull(Me.cboActiontDate) Then
                 strActfDate = "[ActionDate] like '*'"
                 strActtDate = "[ActionDate] like '*'"
         Else
                strActftDate = "[ActionDate] = BETWEEN #" & Format(Me.cboActionfDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.cboActiontDate, "mm\/dd\/yyyy") & "#"
    End If

Can someone see the error?

Regards and happy eastern!

[andy49]

No equals if I remember

Could be is between


Sent from my iPhone using Tapatalk

[Susy]

Yes...

thanks

[Susy]

Hi again,

after andy49 helped me with the date format and sql issue, now I have been fighting with another problem....

when I set one filter from the combo boxes (see image), I am not able to requery the other filters so they show in their lists the only options available.



For example, if I were to select the Status "confirmed", then only name available under the filter "Sales Organizer" should be Mtendere Chakuamba. Right now I get all 9 different names saved in that field.




This is what my form, subform (from the table report) looks like...


and the code...

Private Sub cboStatus_AfterUpdate()


    Call SearchCriteria("[ReportID]")


End Sub


Private Sub cboType_AfterUpdate()
    
    Call SearchCriteria("[ReportID]")


End Sub






Function SearchCriteria(myString As String)


Dim strSOReportNo As String, strStaName As String, strTypeID As String, strSalesOrg As String


Dim strActDate As String, strActfDate As String, strActtDate As String, strActftDate As String






Dim task As String, strCriteria As String


    If IsNull(Me.cboSOReportNo) Then
        strSOReportNo = "[SOReportNo] like '*'"
    Else
        strSOReportNo = "[SOReportNo] = '" & Me.cboSOReportNo & "'"
    End If




    If IsNull(Me.cboStatus) Then
        strStaName = "[StatusName] like '*'"
    Else
        strStaName = "[StatusName] = '" & Me.cboStatus & "'"
    End If


    If IsNull(Me.cboType) Then
        strTypeID = "[TypeID] like '*'"
    Else
        strTypeID = "[TypeID] = " & Me.cboType
    End If
    
    
     If IsNull(Me.cboActionDate) Then
        strActDate = "[ActionDate] like '*'"
    Else
        strActDate = "[ActionDate] = #" & Format(Me.cboActionDate, "mm\/dd\/yyyy") & "#"
    End If
    
    If IsNull(Me.cboActionfDate) Or IsNull(Me.cboActiontDate) Then
        'strActfDate = "[ActionDate] like '*'"
        strActfDate = "[ActionDate] = #" & Format(Me.cboActiontDate, "mm\/dd\/yyyy") & "#"
        strActtDate = "[ActionDate] like '*'"
        strActftDate = "[ActionDate] like '*'"
    Else
        strActftDate = "[ActionDate] BETWEEN #" & Format(Me.cboActionfDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.cboActiontDate, "mm\/dd\/yyyy") & "#"
    End If
    
    
    If IsNull(Me.cboSalesOrg) Then
        strSalesOrg = "[SOrgID] like '*'"
    Else
        strSalesOrg = "[SOrgID] = " & Me.cboSalesOrg
    End If
    
    'myString = "[ReportID]"
      
    'strActDate & " AND " &
    
    strCriteria = strSOReportNo & " AND " & strStaName & " AND " & strTypeID & " AND " & _
                 strActDate & " AND " & strSalesOrg & " AND " & strActftDate
        task = "SELECT * FROM tblReport WHERE " & strCriteria & " ORDER BY " & myString
        Me.subfrmReport.Form.RecordSource = task
        Me.subfrmReport.Form.Requery
        
    


End Function

Do I need to create a query after each afterupdate from each filter?

I really have no idea what to do next...

If someone can help me, that will be great.

P.S.: The code was taken from austin72406, https://www.youtube.com/watch?v=choPri7y_o4

Unfortunately the filters are not updated in his video...


Ideas?

[Susy]

Should I start a new thread?

[Susy]

My date format problem is solved...

Thread is solved!