D Count Problem

[Brogrim]

why am I getting a Name Error

DCount([MemberID], "tblMemFleetDiscount", "[MemberID] >=1))

Can somone explain when you do and don't use "[ and ).

Thanks in Advance

 

[lars7]

Did you try this.

=DCount("[MemberID]", "tblMemFleetDiscount", "[MemberID] >=1")

 

[Remou]

The way these D thingies work is:

[tt]DCount( : Name of D thingy
"[MemberID]", : Name of field to count, it must be
: a string, that is, quoted (""). If
: there is a space in the field name,
: or the field name is a reserved
: word, you must use square
: brackets ([]).Otherwise, it does
: not matter whether you do or don't.
"tblMemFleetDiscount" : Name of the table as a string.
"[MemberID] >=1" : WHERE statement as a string. The
: rules shown above for square
: brackets apply here, too.[/tt]
There is an Or for the WHERE statement, and that is, if the comparison is a control on a form or a variable, you put it outside the quotes. Then it gets a little more complicated, if the comparison is to a text field, you need single quotes (''), if it is a date field, you need hash signs (##) and if it is to a number, you do not need anything. So:

[tt]"[MemberID] =1" : Literally equal to 1
"[MemberID] =" & intI : Equal to a variable intI,
: which might contain 1
"[MemberID] ='hat'" : Literally equal to hat
"[MemberID] ='" & strA & "'" : Equal to a variable strA,
: which might contain hat
"[MemberID] = #01/31/06#" : Literally equal to 01/31/06
"[MemberID] =#" & dteD & "#" : Equal to a variable dteD,
: which might contain 01/31/06[/tt]

I hope I have that right.

 

[dhookom]

Great explanation Remou. If I can add to this without getting too much information:
[tt]
The first argument can be a number of different expressions and isn't limited to a single field.
For instance:
DLookup("[Last Name] & ', ' & [First Name]", "tblEmployees","EmpID=23")
or
DAvg("[HrlyRate]*80","tblWages","Gender='F' AND Status='A' AND HireDate>=#" & dteD & "#")

The WHERE statement evaluates to either true or false.
[/tt]

If you don't have a FAQ like this, you might consider writing one. It would be really helpful.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]