Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003

    Unanswered: DLookup data type mismatch error

    I'm trying to use Dlookup function to lookup a name in another table... thing is when i use a variable as part of the criteria I get a data type mismatch error. I believe this is to do with using the right combination of single and double quotes, but I've tried everything i can think of and still get no joy!

    the following is the code that gives the error:

    If (IsNull(ToAcc)) Then
    SetPayee = "Not a Transfer"
    SetPayee = DLookup("[Name]", "Accounts", "[AccountID] = '" & ToAcc & "'")
    End If

    In the debug window the ToAcc variable is set to 1 so as an experiment i tried the following code:

    SetPayee = DLookup("[Name]", "Accounts", "[AccountID] = 1")

    This works fine.

    Please help... I've wasted hours on this!


  2. #2
    Join Date
    Mar 2003
    Is ToAcc a string and [AccountID] = .. is expecting a number? if so, try CInt(ToAcc)
    Hope it helps!

  3. #3
    Join Date
    Mar 2003
    Bucharest, Romania

    Re: DLookup data type mismatch error

    It' obviously that field [AccountID] is a integer one (or, in any case a numeric one):

    1) Criteria construction for numeric fields:
    "[AccountID] = " & ToAcc

    2) Criteria construction for text fields:

    "[AccountID] = '" & ToAcc & "'"
    Here you must pay attention to the fact that your field value must not contain the single quote character, if it does remove it. For instance if ToAcc has the value Someone's than your condition wold be interpreted like: "[AccountID] = 'Someone's'" the 's part at the end (the string for the condition here is 'Someone') is not an operator or a value of some kind, so it generates an error.

    3) Criteria construction for datetime fields:
    "[DateStart] = #" & [Param] & "#"

    Here the problem is that the literal value of [Param] must meet the Regional Settings format for dates ( mm/dd/yy[yy] or dd/mm/yy[yy] and so on...)

    Good luck!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts