Data type mismatch

[Winston]

We have upgraded our version of Office from 2016 32bit to Office 2019 64 bit. I have been fixing some of the VBA code to work with the 64 bit version. I have ran into an issue, that should be simple, but has me stumped. In the code listed below is what works with Access 2016. The new version of Access I get Run-time error '3664' Data type mismatch in criteria expression. The [prod_line] is a Long Intger, [prod_line_date] is Date/Time, and [prod_line_shift] is a Byte. I have used simalar DLookup in other places in the database but they are not giving me any trouble. Any help would be appreciated. Thank you.

Code: Select all

Private Sub prod_line_shift_Exit(Cancel As Integer)
    Dim dub As Integer
    dub = 0

'   checks to see if the information has been entered already.
'   If the production sheet has already been entered once you cannot enter it again.
    If (Me.c_prod_line <> " ") And (Me.prod_line_date <> " ") And (Me.prod_line_shift <> " ") Then
        dub = DCount("[prod_line]", "1_tb_prod_line_input", "[prod_line] = '" & Me.c_prod_line.Value & "' And [prod_line_date] = '" & Me.prod_line_date.Value & "' And [prod_line_shift] = " & Me.prod_line_shift.Value)
    Else
        Me.c_prod_line.SetFocus
        MsgBox "The Line, Date and, Shift must filled in please.", vbInformation, "Missing Data"
    End If

    If (dub <> 0) Then
        MsgBox "There has been an duplicating error, please check the data.", vbInformation, "Error"
        Me.prod_line_date.SetFocus
    End If
End Sub

These are a couple of thing that I have tried but neither of them works.

Code: Select all

dub = DCount("[prod_line]", "1_tb_prod_line_input", "[prod_line] = '" & Me.c_prod_line.Value & "' And [prod_line_date] = #" & Me.prod_line_date & "# And [prod_line_shift] = " & Me.prod_line_shift)

Also tried

dub = DCount("[prod_line]", "1_tb_prod_line_input", "[prod_line] = '" & Me.c_prod_line & "' And [prod_line_date] = #" & Me.prod_line_date & "# And [prod_line_shift] = " & Me.prod_line_shift)

[HansV]

1) Change

Code: Select all

    If (Me.c_prod_line <> " ") And (Me.prod_line_date <> " ") And (Me.prod_line_shift <> " ") Then

to

Code: Select all

    If Not IsNull(Me.c_prod_line) And Not IsNull(Me.prod_line_date) And Not IsNull((Me.prod_line_shift) Then

2) For dates, you should use #, and format the date as mm/dd/yyyy or yyyy-mm-dd:

Code: Select all

... And [prod_line_date] = #" & Format(Me.prod_line_date, "yyyy-mm-dd") & "# And ...

3) Does it help if you declare dub as Long instead of Integer?

[Winston]

Thank you. Formatting the date did the trick. First, I tried declaring dub as Long but that did not work.


Winston