DCount Syntax

[Guest]

Ok, so I've seen how you use DCount to check the value of a query, but what
is the syntax of the DCount function if you want the criteria in the DCount
statement?

Here's my situation: I have a form named New Lender Legal Name. On that
form is a field named NewLenderLegalName. I want the user to enter the value
into that field, then I want to have the macro check the LenderLegalNames
table to determine if the name is already present. (I know I can use the no
duplicates function of the table to prevent duplicate names, but I won't go
into why they want to include validation at this point in addition to the
table restrictions)

My criteria is as follows:
DCount("[ID]","LenderLegalNames",[Forms]![New Lender Legal
Name]![NewLenderLegalName]=[LenderLegalNames]![LenderLegalName])>0

I'm trying to count the [ID] returns from the table "LenderLegalNames' where
the LenderLegalName is equal to the NewLenderLegalName on my form.

I could have sworn I've used this function and syntax on an old project but
for the life of me I cannot figure out what I'm doing wrong on the criteria.
I don't want to have to have a query out there just to check duplicates of
the name - I'd like to keep it compact and in the macro code.

Thanks!

 

[Guest]

Nevermind...I was looking in the wrong area...general questions has the syntax:

DCount("[ID]","LenderLegalNames","[LenderLegalNames]![LenderLegalName] = '"
& [Forms]![New Lender Legal Name]![NewLenderLegalName] & "'")<1

Just a suggestion to Microsoft...maybe if there was a dedicated section for
just syntax of functions...I know with me at least syntax problems are my
biggest issue (and maybe it's just our company's install, but help for dcount
doesn't return any results).

Thanks all for looking!

 

[tina]

well, i've never seen criteria syntax quite like that. the following should
work as well, as

DCount("[ID]","LenderLegalNames","[LenderLegalName] = '"
& [Forms]![New Lender Legal Name]![NewLenderLegalName] & "'")<1

if you're using the expression in a macro that is called *from* form [New
Lender Legal Name], you should be able to use

DCount("[ID]","LenderLegalNames","[LenderLegalName] = '"
& [NewLenderLegalName] & "'")<1

the syntax for domain aggregate functions isn't too bad, as long as you keep
in mind how the function arguments work. the *second* argument is the name
of a domain - either a table or a query, in your database. the *left side*
of the criteria argument (the third argument) names a field in the domain,
and the *right* side can be about anything you want to compare the field
value to - a hard-coded value, a reference to a form control, an expression.
the *first* argument names a field in the domain that you want the function
to return, or count, as the case may be.

of course, you can get a bit fancier with domain aggregate functions - as
with most things in Access! - but that's the basic structure.

hth

Nevermind...I was looking in the wrong area...general questions has the syntax:

DCount("[ID]","LenderLegalNames","[LenderLegalNames]![LenderLegalName] = '"
& [Forms]![New Lender Legal Name]![NewLenderLegalName] & "'")<1

Just a suggestion to Microsoft...maybe if there was a dedicated section for
just syntax of functions...I know with me at least syntax problems are my
biggest issue (and maybe it's just our company's install, but help for dcount
doesn't return any results).

Thanks all for looking!

Ok, so I've seen how you use DCount to check the value of a query, but what
is the syntax of the DCount function if you want the criteria in the DCount
statement?

Here's my situation: I have a form named New Lender Legal Name. On that
form is a field named NewLenderLegalName. I want the user to enter the value
into that field, then I want to have the macro check the LenderLegalNames
table to determine if the name is already present. (I know I can use the no
duplicates function of the table to prevent duplicate names, but I won't go
into why they want to include validation at this point in addition to the
table restrictions)

My criteria is as follows:
DCount("[ID]","LenderLegalNames",[Forms]![New Lender Legal
Name]![NewLenderLegalName]=[LenderLegalNames]![LenderLegalName])>0

I'm trying to count the [ID] returns from the table "LenderLegalNames' where
the LenderLegalName is equal to the NewLenderLegalName on my form.

I could have sworn I've used this function and syntax on an old project but
for the life of me I cannot figure out what I'm doing wrong on the criteria.
I don't want to have to have a query out there just to check duplicates of
the name - I'd like to keep it compact and in the macro code.

Thanks!