DLookup Question

[Ebes1099]

I'm trying to pull a value into one of my forms and most of the values are stored in 1 table. But there are a few exceptions where there is no record for a group in that table and I need to pull it from a different table. I can't quite figure out how to do that. I was thinking about using an isNull but will that work if it just doesn't find a match on GroupNumber and Package in the "Claims" table? Or is there another function I can use where I check to see if the GroupNumber-Package (Primary Key is a combo on those 2 fields) exists in the "Claims" table and if it doesn't, pull the value from this other table?

=DLookUp("[CLAIMS_AVE_PMPM]","Claims","GroupNumber=valGroupNumber AND Package=valPackage")

 

[MajP]

if you wanted to do it in a calculated control you could wrap it in an iif function

iif(expression,value if true, value if false)

if Dlookup does not find anything it returns a null. So you could check the first dlookup to see if it returns a null. If it does not return a null then it equals the first dlookup, else it equals the second. Writing this is doable but ugly

so you could do something like

iif(not isnull(your first dlookup),Yourfirstdlookup,yourseconddlookup)


Much cleaner to build a function and call it

public function getPMPM (grpNumber as variant,package as variant)
dim strWhere as string
grpNumber = NZ(grpNumber,0)
package = NZ(grpNumber,0)
'I assume this is really what you want below
strWhere = "GroupNumber = " & valGroupNumber & " AND Package = " & valPackage
if not isnull(DLookUp("[CLAIMS_AVE_PMPM]","Claims",strWhere) then
getPMPM = (DLookUp("[CLAIMS_AVE_PMPM]","Claims",strWhere)
else
getPMPM = Dlookup(.....)
end if
end function

in your control
=getPMPM ([grpNumberField],[packagefield])