Access Dlookup data type mismatch in criteria expression

617 Views Asked by At

i have a problem with DLookup function

Dlookup("exist", "contracts_periodic", strwhere) and every time gives data type mismatch in criteria expression

exist in numeric

contract ID is numeric

Any Help?

Dim uu As Integer     '------ no of periodic visits----
uu = DLookup("[Periodic_visits] ", "[contracts]", "[contract_ID]=[Forms]![Contracts]![contract_ID]")
Dim ss As Integer    ' ------ no of visits assigned in the table ----
ss = DCount("[visit_date]", "[contracts_periodic]", "[contract_ID]=[Forms]![Contracts]![contract_ID]")
Dim kk As Integer
kk = uu - ss

    If kk > 0 Then
        Dim ax  ' ----- contract period ----
        ax = DLookup("[contract_period] ", "[contracts]", "[contract_ID]=[Forms]![Contracts]![contract_ID]")
        Dim az  ' ----- period type 1 Year & 2 = Month -----
        az = DLookup("[period_type] ", "[contracts]", "[contract_ID]=[Forms]![Contracts]![contract_ID]")
        Dim frec As Integer       ' ---- get every ? month ---
        Dim ax_month As Integer   ' ---- get contract period / Month------

            If az = 2 Then
                ax_month = ax * 12
                frec = ax_month / uu
            ElseIf az = 1 Then
                ax_month = ax
                frec = ax_month / uu
            End If

        Dim x_start As Date   '--------- contract start date
        x_start = DLookup("[start_date] ", "[contracts]", "[contract_ID]=[Forms]![Contracts]![contract_ID]")
        Dim date_now As Date
        Dim i As Integer
        For i = 1 To uu
        date_now = DateAdd("m", i * frec, x_start)
                Me.AllowAdditions = True
                Dim strwhere As String
                strwhere = "([contract_ID]=" & [Forms]![Contracts]![contract_ID] & ") and ([visit_date]= #" & date_now & "#)"
                Dim kkkkk As Integer
                kkkkk = DLookup("exist", "contracts_periodic", strwhere)
                If IsNull(kkkkk) Then
                Dim sqlst As String
                sqlst = "insert into contracts_periodic (contract_Id,visit_date) values ([Forms]![Contracts]![contract_ID] , '" & date_now & "');"
                DoCmd.SetWarnings False
                DoCmd.RunSQL sqlst
                Me.Requery
                Me.AllowAdditions = False
                End If
        Next i
    End If
0

There are 0 best solutions below