DCount multiple tables

[kstoltz1979]

I have multiple tables. Both have a similar unit number. What I want to do is match those and then have criteria like for one have it check to make sure Renew is Yes and for the other one say when Lease Exp_Date is end of this month. Here is the code I have and it does not work.

=DCount("[Lease Exp_Date]","Tenant Info","[Lease Exp_Date] = DateSerial(Year(Date()), Month(Date()) +1,0)") And DCount("[Renew]","LseRenew","([Renew] = Yes")

When I run them DCount's seperately I get the answers I expect I just simply can't get them to work together. Any help is much appreciated I'm really killing myself trying to get this answer. Thanks! Kyle

 

[Guus2005]

there are e few errors:

=DCount("[Lease Exp_Date]","Tenant Info","[Lease Exp_Date] = DateSerial(Year(Date()), Month(Date()) +1,0)") And DCount("[Renew]","LseRenew","[COLOR="Red"][B]([Renew] = Yes[/B]")[/COLOR]

The number of parenthess doesn't match

=DCount("[Lease Exp_Date]","Tenant Info","[Lease Exp_Date] = DateSerial(Year(Date()), Month(Date()) +1,0)") [COLOR="Red"][B]And DCount("[Renew]","LseRenew","([Renew] = Yes")[/B][/COLOR]

AND Dcount(...) results in AND 2. 2 being the result of the dcount function.

Advice: execute the complete line in your immediate window and observe the error messages.

HTH

 

[kstoltz1979]

Thanks Guus2005, I just decided to create 2 queries that have my data that I need and then call those queries up in my control source and that worked perfectly. I was killing myself with DCount so decided to try another avenue in creating queries and using SQL code to get the data I need to copy to my form.