Help with Date Code

[Taff]

Hi All,

I have a form with a ListBox and command button on. When I click the command button it deletes the record from the ListBox with the following on the OnClick Event:-

    Const strSQLDelete = "DELETE * FROM [tblClaim] "
    Dim strCriteria As String
    Dim strPrompt As String
    
    Const Title = "Warning!"
    Const Buttons = vbYesNo + vbQuestion
    
    If IsNull(Me.ListLearnerFunding) Then Exit Sub
    
    strCriteria = _
        "WHERE " & _
        "[learn_id] = """ & Me.ListLearnerFunding.Column(0) & """ AND " & _
        "[provi_id] = """ & Me.ListLearnerFunding.Column(1) & """ AND " & _
        "[lprog_id] = """ & Me.ListLearnerFunding.Column(2) & """ AND " & _
        "[ContractID] = """ & Me.ListLearnerFunding.Column(3) & """ AND " & _
        "[ProductID] = """ & Me.ListLearnerFunding.Column(4) & """ AND " & _
        "[ClaimDate] = """ & Me.ListLearnerFunding.Column(6) & """ "

    strPrompt = "Are you sure you want to Delete this Funding?"
    
    If Me.ListLearnerFunding.ItemsSelected.Count > 0 Then
        If MsgBox(strPrompt, Buttons, Title) = vbYes Then
            With DoCmd
                                    .SetWarnings False
                    .RunSQL strSQLDelete & strCriteria
                    .SetWarnings True
              
            End With
            Me.ListLearnerFunding.Requery
        End If
    End If

The Problem I am having is when I click the command button it deletes all the rows as they have the same Learn_ID, Provi_ID, Lprog_ID, ContractID and ProductID. So to get around this I added the date as criteria and now i get the error "Data Type Mismatch in Criteria Expression".

So I beleive my problem lies with the line

"[ClaimDate] = """ & Me.ListLearnerFunding.Column(6) & """ "

as it is a date field.

Any Ideas?

Thanks for your help,


Anthony

 

[Mile-O]

The method you have used is for text criteria.

For a date you have to use the # delimiter.

"[ClaimDate] = #" & Me.ListLearnerFunding.Column(6) & "# "

As an extra caution, use the CDate() function too:

"[ClaimDate] = #" & CDate(Me.ListLearnerFunding.Column(6)) & "# "

 

[Taff]

Thanks Mile-O-Phile,

Works Great.

Ant.