DLookUp MATCH

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that I would like to pull the data for from a Query. As it is
now the Query has two fields Co_RSID and SumOfCo_Count so I get the info like
this:

Co_RSID SumOfCo_Count
7E4 20
7E5 34

I know how to do a DLookup if I only have one record in the Query. I don’t
know how to tell it to return the value for of 20 for 7E4 or a 0 if 7E4 is
not in the Query.

=DLookUp("[SumOfCo_Count] ","PL_APPT_Made_Action_Count_Co_Sum_Q")

Thanks

Keith
 
The DLookup
Dlookup("FieldName","TableName","WhereCriteria")

So try
Co_RSID SumOfCo_Count
7E4 20
7E5 34

I know how to do a DLookup if I only have one record in the Query. I don’t
know how to tell it to return the value for of 20 for 7E4 or a 0 if 7E4 is
not in the Query.

=DLookUp("[SumOfCo_Count] ", "PL_APPT_Made_Action_Count_Co_Sum_Q","Co_RSID =
'7E4'")

Or, if you want to retrieve the value using a variant
Dim MyCo_RSID as String
MyCo_RSID = "7E4"
=DLookUp("[SumOfCo_Count] ", "PL_APPT_Made_Action_Count_Co_Sum_Q","Co_RSID =
'" & MyCo_RSID & "'")
 
KAnoe,

You might also look into the DSUM function...
as in:
DSUM(<Summary Field Expression>,
<Domain Expression>,
[<Criteria Expression>])
or, in your instance, something like:
mySum = nz(DSUM("[SumOfCo_Count]", _
"PL_APPT_Made_Action_Count_Co_Sum_Q", _
"Co_RSID = '" & MyCo_RSID & "'") _
, 0)
(of course, since the DSUM can add the total up for you, you may want to
change the field and query from a summary query to a different query or a
table and field directly since there's usually little need to add the sum up
twice... Note that the criteria expression can be as complex a where
expression as you'd like with all the ANDs ORs and ()s you want to use, and I
believe the <Domain Expression> can also be any valid FROM query expression,
including inner and outer join expressions)

Note that the DSUM function can return NULL (if there are no qualifying
records). The NZ function I used above turns that possible Null return value
into a ZERO for a mysum value.

Ofer said:
The DLookup
Dlookup("FieldName","TableName","WhereCriteria")

So try
Co_RSID SumOfCo_Count
7E4 20
7E5 34

I know how to do a DLookup if I only have one record in the Query. I don’t
know how to tell it to return the value for of 20 for 7E4 or a 0 if 7E4 is
not in the Query.

=DLookUp(" ", "PL_APPT_Made_Action_Count_Co_Sum_Q","Co_RSID =
'7E4'")

Or, if you want to retrieve the value using a variant
Dim MyCo_RSID as String
MyCo_RSID = "7E4"
=DLookUp("[SumOfCo_Count] ", "PL_APPT_Made_Action_Count_Co_Sum_Q","Co_RSID =
'" & MyCo_RSID & "'")


--
\\// Live Long and Prosper \\//
BS"D


KAnoe said:
I have a field that I would like to pull the data for from a Query. As it is
now the Query has two fields Co_RSID and SumOfCo_Count so I get the info like
this:

Co_RSID SumOfCo_Count
7E4 20
7E5 34

I know how to do a DLookup if I only have one record in the Query. I don’t
know how to tell it to return the value for of 20 for 7E4 or a 0 if 7E4 is
not in the Query.

=DLookUp("[SumOfCo_Count] ","PL_APPT_Made_Action_Count_Co_Sum_Q")

Thanks

Keith
Click to expand...
Click to expand...
 
Back
Top