By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

how to DCount in query using another field of the query as the criteria?

100+
P: 176
Good afternoon. I'm stucked in composing the syntax for DCount expression in a select query. The query qryCustomers has CustomerID field, the DCount function uses tblLoans with LoanDate and Id fields and the expression field should be: DCount("LoanDate","tblLoans","Id=CustomerID"), but the query says it can't find the expression parameter.

note: the query does work if the experssion uses an ID number for the criteria, for example: DCount("LoanDate","Loans","Id=643")

Thanks for you kind help,
Michael.
Feb 1 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,335
Good afternoon. I'm stucked in composing the syntax for DCount expression in a select query. The query qryCustomers has CustomerID field, the DCount function uses tblLoans with LoanDate and Id fields and the expression field should be: DCount("LoanDate","tblLoans","Id=CustomerID"), but the query says it can't find the expression parameter.

note: the query does work if the experssion uses an ID number for the criteria, for example: DCount("LoanDate","Loans","Id=643")

Thanks for you kind help,
Michael.
The problem is the quotes. Nothing is evaluated if it is enclosed in quotes. What you want is DCount("LoanDate","tblLoans","Id=" & CustomerID).
Feb 1 '07 #2

ADezii
Expert 5K+
P: 8,615
Good afternoon. I'm stucked in composing the syntax for DCount expression in a select query. The query qryCustomers has CustomerID field, the DCount function uses tblLoans with LoanDate and Id fields and the expression field should be: DCount("LoanDate","tblLoans","Id=CustomerID"), but the query says it can't find the expression parameter.

note: the query does work if the experssion uses an ID number for the criteria, for example: DCount("LoanDate","Loans","Id=643")

Thanks for you kind help,
Michael.
If CustomerID is a String then:
Expand|Select|Wrap|Line Numbers
  1. DCount("LoanDate","tblLoans","Id='" & CustomerID &"'") 
Feb 1 '07 #3

100+
P: 176
That is right. Thanks.
Feb 1 '07 #4

NeoPa
Expert Mod 15k+
P: 31,306
Rabbit - I have my eye on you.
You're becoming a real asset to the community! :)
Feb 1 '07 #5

Rabbit
Expert Mod 10K+
P: 12,335
Thanks! Not as advanced as some of the others here but I try.
Feb 1 '07 #6

NeoPa
Expert Mod 15k+
P: 31,306
Advancement is an ongoing thing.
Answering things you know is still a big contribution.
The more you know - the bigger it gets :) (or should that be :()
Feb 1 '07 #7

ADezii
Expert 5K+
P: 8,615
Advancement is an ongoing thing.
Answering things you know is still a big contribution.
The more you know - the bigger it gets :) (or should that be :()
This Rabbit is multiplying rabbitly (LOL).
Feb 1 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.