DLookup Function Criteria?

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
 
S

Sandra Daigle

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())
 
R

Rick Brandt

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 & "")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Min Function within DLookup Function 0
DLookup help 6
DLookUp 7
Dlookup error 4
Dlookup on pivot chart field 0
DLookUp Multiple Criteria 4
Dlookup 4
DLookup in Text box to show Currency (number field) 9

Top