Dlookup

[hyp527]

Hi I'm new to Access and would like to use the Dlookup to set criteria to look up some data. I heard this is a valueable function. Can someone provide an example, possibility a database uploaded for me to see. I don't understand the sql language.

Thanks.

 

[Mike375]

If you search this forum or Google on DLookup you will get heaps of stuff.

Here is one example

=DLookUp("[4NS2]","onesixty","[AgeNB]=[Forms]![PForm]![anb]")

That is in an unbound textbox on the form PForm

It is saying.....go to the table "onesixty" and to the field [4NS2] and the record where the value in [AgeNB] is equal to the vale in [anb] on the form PForm. In this case the table "onesixty" has a lot of insurance premium rates and [AgeNB] and [anb] are "age next birthday" and [4NS2] has the premium rates for a certain type of policy.

That should help gte you started.

 

[boblarson]

And in reality - to make sure it works consistently, you should keep the reference to the controls OUTSIDE of the quotes:

=DLookUp("[4NS2]","onesixty","[AgeNB]=" & [Forms]![PForm]![anb])

 

[Mike375]

Bob,

That looks the sort of thing I use to reference the value in a field for opening a Word. doc etc

So is the potential problem with the way I do (or was doing) Dlookup that [Forms]![PForm]![anb] will be read as unit or value as opposed to the value in [anb]

 

[boblarson]

Bob,

That looks the sort of thing I use to reference the value in a field for opening a Word. doc etc

So is the potential problem with the way I do (or was doing) Dlookup that [Forms]![PForm]![anb] will be read as unit or value as opposed to the value in [anb]

Adam used to claim that it worked for him (the way you had it) but it never did for me and I never had to worry that it would be misunderstood by Access if done the way I showed. Yes, it is possible that it will read it as a literal instead of as the value of the control on the form unless you concatenate it in as I had listed.

 

[Mike375]

Maybe it works because it is This = That

Like SetValue in VBA

I have this for OutLook

.To = Forms!PrintAndClose!EMStore
.Subject = [Forms]![PrintAndClose]![Text446]


I have never had any problem with DLookup and have used the same for years. 99.9% of mine are in the Expression for SetValue Macro actions.

But I have some DLookups I am making at the moment and I will switch to what you have because as you say it rules out the possibility.