DLookup Function Criteria?

  • Thread starter Thread starter Leonard
  • Start date Start date
L

Leonard

I have tried to use the DLookUp Funciton using a module
level variable as the criteria. When I do, Access keeps
putting brackets around the variable name MyModVar.

=DLookup("[MyName]", "MyTable", "[MyID] = " & MyModVar)

Any and all help welcome

Thanks

Leonard
 
Where are you using this Dlookup? A module level variable is only available
within a VBA procedure or function. Thus this syntax will not work within a
query or in a property for a control (ie the ControlSource or DefaultValue
Property). You will either need to revamp the statement to use VBA or use a
little workaround to get the global variable.

The workaround involves creating a Public Function whose sole purpose is to
return the value of the global variable. Then your Dlookup statement can
call the function to get the global:

Public Function getMyModVar() as Long
getMyModVar=myModVar
end function

=DLookup("[MyName]", "MyTable", "[MyID] = " & getMyModVar())
 
Leonard said:
I have tried to use the DLookUp Funciton using a module
level variable as the criteria. When I do, Access keeps
putting brackets around the variable name MyModVar.

=DLookup("[MyName]", "MyTable", "[MyID] = " & MyModVar)

=DLookup("[MyName]", "MyTable", "[MyID] = " & MyModVar & "")
 
Back
Top