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 & "'")
In the debug window the ToAcc variable is set to 1 so as an experiment i tried the following code:
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...)