function with dlookup - error 2471

[LGarcia]

Hi all,
I have a field in queryA that calls a function. In this function I do a
dcount on queryB. If the count of queryB = 0 then a 0 is returned to
queryA. If the count of queryB > 0 I'd like to return a value from queryB
where subject and date match the same in queryA. Something like this:

Function MyVal()

If dcount("*", "queryB") > 0 then
MyVal = Dlookup("var1", "queryB", "Subject=queryA.Subject AND
IntDate=queryA.IntDate"
Else
MyVal = 0
Endif

I'm geting an error 2471. Is there a better way to do this?
Hope someone can help.
TIA,
LGarcia

 

[pietlinden]

Hi all,
I have a field in queryA that calls a function. In this function I do a
dcount on queryB. If the count of queryB = 0 then  a 0 is returned to
queryA. If the count of queryB > 0 I'd like to return a value from queryB
where subject and date match the same in queryA. Something like this:

Function MyVal()

If dcount("*", "queryB") > 0 then
    MyVal = Dlookup("var1", "queryB", "Subject=queryA.Subject AND
IntDate=queryA.IntDate"
Else
MyVal = 0
Endif

I'm geting an error 2471. Is there a better way to do this?
Hope someone can help.
TIA,
LGarcia

Forget the technical details of how you're trying to do this. What is
your ultimate goal?

 

[LGarcia]

Ultimate goal is to calculate a variable in queryA using a value from queryB
if it exists. If there is no data produced in queryB then return a 0 to use
in the calculation.

Thanks for the quck reply.

Hi all,
I have a field in queryA that calls a function. In this function I do a
dcount on queryB. If the count of queryB = 0 then a 0 is returned to
queryA. If the count of queryB > 0 I'd like to return a value from queryB
where subject and date match the same in queryA. Something like this:

Function MyVal()

If dcount("*", "queryB") > 0 then
MyVal = Dlookup("var1", "queryB", "Subject=queryA.Subject AND
IntDate=queryA.IntDate"
Else
MyVal = 0
Endif

I'm geting an error 2471. Is there a better way to do this?
Hope someone can help.
TIA,
LGarcia

Forget the technical details of how you're trying to do this. What is
your ultimate goal?

 

[LGarcia]

Figured out one part:
I can't use queryA as criteria. I need to use the table referenced in
queryA.