data type mismatch in criteria expression

[adaniele]

hi guys, here is my problem.
the error in the title appears when i try to update a record.
the field MODON in the table is defined as a short date and an input date 00/00/0000.

Here is the code where i insert and update the records:

      Option Compare Database
Option Explicit

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCalendar"
    DoCmd.OpenForm "frmCalendar", , , , , acDialog, Me.name & ";modon"

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.description
    Resume Exit_Command4_Click
    
End Sub
Private Sub Command15_Click()
Dim str1 As Date

On Error GoTo Err_Command15_Click

        'DoCmd.SetWarnings False
        
        If DCount("[comment]", "comments", "[change_id]=" & Me.Task_Num & "") = 0 Then
            MsgBox "insert"
            DoCmd.RunSQL "insert into [comments] (change_id,modon,comment,modby,modif) values (" & Me.Task_Num.Value & ",'" & Me.modon.Value & "','" & Me.Text10.Value & "','" & Forms!Login!username1 & "','" & Now() & "');"
            DoCmd.SetWarnings True
        Else
            MsgBox "update"
            MsgBox DLookup("[comment]", "comments", "[modon]='" & CStr(Me.modon.Value) & "'")
            DoCmd.RunSQL "update [comments] set comment='" & Me.Text10.Value & "' where change_id=" & Me.Task_Num & " and [modon]='" & Format(str1, "dd/mm/yyyy") & "';"
            DoCmd.RunSQL "update [comments] set modby='" & Forms!Login!username1 & "' where change_id=" & Me.Task_Num & " and [modon]='" & Me.modon.Value & "';"
            DoCmd.RunSQL "update [comments] set modif='" & Now() & "' where change_id=" & Me.Task_Num & " and [modon]='" & Me.modon.Value & "';"
        End If
        DoCmd.SetWarnings True
        
Exit_Command15_Click:
    Exit Sub

Err_Command15_Click:
    MsgBox Err.description
    Resume Exit_Command15_Click
    
End Sub
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click


    DoCmd.Close

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.description
    Resume Exit_Command16_Click
    
End Sub

Private Sub Command4_Exit(Cancel As Integer)
    If IsEmpty(Me.modon) Then
        MsgBox "Please, select a date for the minute"
        Me.modon.SetFocus
    Else
        Me.Task_Num.Visible = True
    End If
End Sub

Private Sub Form_Load()
    Me.Text13.Visible = False
    Me.Text10.Visible = False
    Me.Label12.Visible = False
    Me.Label9.Visible = False
    Me.Task_Num.Visible = False
End Sub
Private Sub task_num_AfterUpdate()

    If IsEmpty(Me.Task_Num) Then
        MsgBox "Please, select a task Number"
        Me.Task_Num.SetFocus
    Else
        Me.Text13.Visible = True
        Me.Text10.Visible = True
        Me.Label12.Visible = True
        Me.Label9.Visible = True
        Me.Text13 = DLookup("[description]", "newchange", "[change_id]=" & Me.Task_Num & "")
        Me.text10 = DLookup("[comment]", "comments", "[change_id]=" & Task_Num & " and [modon]='" & Me.modon.value & "'")

    End If
End Sub

thx in advance....

 

[adaniele]

well you may need more details.......
this is what i am trying to do.
I create a form, where a date control box is filled with a calendar. Then, the user choose an open task from a combo (the data from this combo comes from a query). The description control box is populated with the tasks description (from table changes) and populate the comment field IF there is a record for that task number and the date choosen from the calendar , in comments table.
The thing is , when i use the dcount to know if a record exists for those conditions the error appears, but if i remove the condition related with the date it works ( of course will bring the first record for the task, but the dcount works). So, the problems seems to be the condition for the date.

I added the following code and it print the msg "=".

If DLookup("[modon]", "comments") = Me.modon.Value Then
MsgBox "="
End If

thx

 

[adaniele]

hi everyone........i solved the problem. Here is the solution....

I change the old dlookup by this one.

DLookup("[comment]", "comments", "[change_id]=" & Task_Num & " and [modon]=#" & Me.modon & "#")

thx max