Need Help About Combobox With Dates

[DanielM]

I need help urgent. I don't know how to filter a form with combobox By dates', cause I understood that the value of combobox should be a number, so what should I do if my values are dates? Please Help!

[maxamis4]

Combo boxes are controls bound to queries. You should be able to go to the properties of the combo box and go to its control source and have it sort the date by descending value. That should sort the combo box for you.

[Rabbit]

Combo boxes don't have to be a number. But aside from that, Dates are stored as a number. So either way, no problem.

[NeoPa]

Check out (Example Filtering on a Form.) for the full information on this topic.

[DanielM]

Check out (Example Filtering on a Form.) for the full information on this topic.

Thanks a lot.my problem was solved. I have another problem if you can help me. I am filtyering a form by few combo boxes by creating a long string from all the values of the combos. at the last combo I get a RunTimeError 2001:"You Cnceled previouse Operation". what's that mean?

[DanielM]

Combo boxes don't have to be a number. But aside from that, Dates are stored as a number. So either way, no problem.

Thanks a lot.You was absoloute correct. my problem was solved. I have another problem if you can help me. I am filtering a form by few combo boxes by creating a long string from all the values of the combos. at the last combo I get a RunTimeError 2001:"You Cnceled previouse Operation". what's that mean?

[NeoPa]

Perhaps you could post the code you use for this, just to give us a clue.

[DanielM]

Perhaps you could post the code you use for this, just to give us a clue.

Thanks a lot.This is the Code:

Private Sub FilterProcessForm()
 
    Dim strTotalFilter As String
    Dim strMonthFilter As String
    Dim strDepartmentFilter As String
    Dim strProjectTypeFilter As String
    Dim strCustomerFilter As String
    Dim strProjectFilter As String
    Dim strInvoiceFilter As String
 
    '--------MonthFilter
    If IsNull(Trim(cmbSearchByProcessMonth.Value)) Then
        ShowMessegeBox "לא נרשם תאריך חוקי"
        Exit Sub
    End If
    strMonthFilter = "fldmpsProcessMonth =" & "#" & cmbSearchByProcessMonth.Value & "#"
 
    '--------DepartmentFilter
    If cmbSearchByDepartment.Value = -999 Or IsNull(Trim(cmbSearchByDepartment.Value)) Then
        strDepartmentFilter = ""
    Else
        strDepartmentFilter = "flddipDepartmentCode=" & cmbSearchByDepartment.Value & " AND "
    End If
 
 
    '--------ProjectTypeFilter
    If cmbSearchByProjectTypeCode.Value = -999 Or IsNull(Trim(cmbSearchByProjectTypeCode.Value)) Then
        strProjectTypeFilter = ""
    Else
        strProjectTypeFilter = "flddipProjectTypeCode=" & cmbSearchByProjectTypeCode.Value & " AND "
    End If
 
 
    '--------CustomerFilter
    If cmbSearchByCustomer.Value = -999 Or IsNull(Trim(cmbSearchByCustomer.Value)) Then
        strCustomerFilter = ""
    Else
        strCustomerFilter = "fldcicCustInternaCode=" & cmbSearchByCustomer.Value & " AND "
    End If
 
     '--------ProjectFilter
    If cmbSearchByProject.Value = -999 Or IsNull(Trim(cmbSearchByProject.Value)) Then
        strProjectFilter = ""
    Else
        strProjectFilter = "fldmpsDepartmentProjectCode =" & cmbSearchByProject.Value & " AND "
    End If
 
    '--------InvoiceFilter
    If cmbInvoiceNumber.Value = -10000 Or IsNull(Trim(cmbInvoiceNumber.Value)) Then
        strInvoiceFilter = ""
    Else
        strInvoiceFilter = "fldmpsInvoiceNumber=" & cmbInvoiceNumber.Value & " AND "
    End If
 
    '--------TotalFilter
    strTotalFilter = strDepartmentFilter & strProjectTypeFilter & strCustomerFilter & strProjectFilter & strInvoiceFilter & strMonthFilter
 
    Me.Filter = strTotalFilter  //   Here I Get The Error.
    Me.FilterOn = True
End Sub

-----------------------------------------------------------
I checked The "TotalFilter" and was no problem.?????????

[NeoPa]

The code mainly seems fine to me if [fldmpsProcessMonth] is a Date/time field and all the other fields are numeric.
There is no point in saying IsNull(Trim()) as that is the same as IsNull().
I've done some minor tidy-ups for you but I haven't fixed anything fundamental as I don't have the field types yet.

What are the types of all the fields?

Private Sub FilterProcessForm()
 
    Dim strTotalFilter As String
    Dim strMonthFilter As String
    Dim strDepartmentFilter As String
    Dim strProjectTypeFilter As String
    Dim strCustomerFilter As String
    Dim strProjectFilter As String
    Dim strInvoiceFilter As String
 
    '--------MonthFilter
    If IsNull(Me!cmbSearchByProcessMonth) Then
        ShowMessegeBox "?? ???? ????? ????"
        Exit Sub
    End If
    strMonthFilter = "fldmpsProcessMonth=" & "#" & Me!cmbSearchByProcessMonth & "#"
 
    '--------DepartmentFilter
    If Me!cmbSearchByDepartment = -999 Or IsNull(Me!cmbSearchByDepartment) Then
        strDepartmentFilter = ""
    Else
        strDepartmentFilter = "flddipDepartmentCode=" & Me!cmbSearchByDepartment & " AND "
    End If
 
 
    '--------ProjectTypeFilter
    If Me!cmbSearchByProjectTypeCode = -999 Or IsNull(Me!cmbSearchByProjectTypeCode) Then
        strProjectTypeFilter = ""
    Else
        strProjectTypeFilter = "flddipProjectTypeCode=" & Me!cmbSearchByProjectTypeCode & " AND "
    End If
 
 
    '--------CustomerFilter
    If Me!cmbSearchByCustomer = -999 Or IsNull(Me!cmbSearchByCustomer) Then
        strCustomerFilter = ""
    Else
        strCustomerFilter = "fldcicCustInternaCode=" & Me!cmbSearchByCustomer & " AND "
    End If
 
     '--------ProjectFilter
    If Me!cmbSearchByProject = -999 Or IsNull(Me!cmbSearchByProject) Then
        strProjectFilter = ""
    Else
        strProjectFilter = "fldmpsDepartmentProjectCode=" & Me!cmbSearchByProject & " AND "
    End If
 
    '--------InvoiceFilter
    If Me!cmbInvoiceNumber = -10000 Or IsNull(Me!cmbInvoiceNumber) Then
        strInvoiceFilter = ""
    Else
        strInvoiceFilter = "fldmpsInvoiceNumber=" & Me!cmbInvoiceNumber & " AND "
    End If
 
    '--------TotalFilter
    strTotalFilter = strDepartmentFilter & _
                     strProjectTypeFilter & _
                     strCustomerFilter & _
                     strProjectFilter & _
                     strInvoiceFilter & _
                     strMonthFilter
 
    Me.Filter = strTotalFilter  //   Here I Get The Error.
    Me.FilterOn = True
End Sub

[DanielM]

Thanks a lot for your kind help.
All the variables are int, beside the ProcessMonth which is DateTime
It' s the third form I do with few combos that are filtering together the form' and never got such error. pls help.

[NeoPa]

In that case try this :

Private Sub FilterProcessForm()
 
    Dim strFilter As String
 
    '--------MonthFilter
    If IsNull(Me!cmbSearchByProcessMonth) Then
        ShowMessegeBox "?? ???? ????? ????"
        Exit Sub
    End If
 
    strFilter = "(fldmpsProcessMonth=" & _
                Format(Me!cmbSearchByProcessMonth,'\#m/d/yyyy\#)')
 
    '--------DepartmentFilter
    If Nz(Me!cmbSearchByDepartment,-999) <> -999 Then _
        strFilter = strFilter & " AND (" & _
                "flddipDepartmentCode=" & Me!cmbSearchByDepartment & ")"
 
    '--------ProjectTypeFilter
    If Nz(Me!cmbSearchByProjectTypeCode,-999) = -999 Then _
        strFilter = strFilter & " AND (" & _
                "flddipProjectTypeCode=" & Me!cmbSearchByProjectTypeCode & ")"
 
    '--------CustomerFilter
    If Nz(Me!cmbSearchByCustomer,-999) <> -999 Then _
        strFilter = strFilter & " AND (" & _
                "fldcicCustInternaCode=" & Me!cmbSearchByCustomer & ")"
 
     '--------ProjectFilter
    If Nz(Me!cmbSearchByProject,-999) <> -999 Then _
        strFilter = strFilter & " AND (" & _
                "fldmpsDepartmentProjectCode=" & Me!cmbSearchByProject & ")"
 
    '--------InvoiceFilter
    If Nz(Me!cmbInvoiceNumber,-10000) <> -10000 Then _
        strFilter = strFilter & " AND (" & _
                "fldmpsInvoiceNumber=" & Me!cmbInvoiceNumber & ")"
 
 
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub