0

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
Amr Aly
  • 26
  • 3
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) (Hint: the Date variable is the problem) – Andre Dec 07 '17 at 13:36
  • And also change data type of `kkkkk` variable to `Variant`, integer cannot be null – Sergey S. Dec 07 '17 at 15:45
  • Change `[visit_date]= #" & date_now & "#` to `[visit_date]= #" & Format(date_now, "mm\/dd\/yyyy") & "#` – Sergey S. Dec 07 '17 at 15:47
  • @SergeyS. thanks for your help .... already tried variant and change the date format as you mentioned but still have the error – Amr Aly Dec 07 '17 at 19:07
  • @Andre .. thanks for the link .. I've tired every thing regarding to the date variable and still don't know what i'm missing .. any help ??? – Amr Aly Dec 07 '17 at 19:10
  • What is data type of `[contract_ID]` column? If it's text, use `strwhere = "([contract_ID]='" & [Forms]![Contracts]![contract_ID] & "')...` – Sergey S. Dec 08 '17 at 04:21
  • @SergeyS. thanks for your help , i totally forget that [contract_ID] is text and used to deal with it as numeric so it gives error ... now the error is gone thanks man another question regarding to date : [kkkkk] now gives null even if criteria match -- the issue with the date i changed the criteria to DLookup("[exist]", "[contracts_periodic]", "[visit_date] = #" & Format(1 / 4 / 2017, "dd\/mm\/yyyy") & "#") it gives null although the criteria match ... visit date is date/time and short date format.. any idea ??? thanks in advance – Amr Aly Dec 08 '17 at 15:17
  • just now make it done :) couldn't done it without your help @SergeyS. – Amr Aly Dec 08 '17 at 20:47
  • `DLookup("[exist]", "[contracts_periodic]", "[visit_date] = #" & Format(#1/4/2017#, "dd\/mm\/yyyy") & "#")` – Sergey S. Dec 09 '17 at 10:52

0 Answers0