DSUM syntax problem

[connieharper]

I have a form that contains a calculated field. I want it
to display a total for all records in the tbl_accounting
table for the employee that is displayed on the form and
for the type displayed on the form. I have used the
following dsum statement in the controlsource property of
this field:

=DSum("[amount]","Tbl_accounting","[type] =[charge]"
And "[student_id] = [employeeid]")

It comes back with a grand total of all the records in the
table and doesn't limit the selection based on the TYPE or
the EMPLOYEE.

If I remove the AND part of the statement, it works. It
comes back with a total of all the records in the table
that have a TYPE = to the TYPE on the form. But I want to
also limit the selection of records to the employee who is
displayed on the form. I guess my syntax is wrong. Anyone
know what is wrong with it?

Thanks

 

[fredg]

I have a form that contains a calculated field. I want it
to display a total for all records in the tbl_accounting
table for the employee that is displayed on the form and
for the type displayed on the form. I have used the
following dsum statement in the controlsource property of
this field:

=DSum("[amount]","Tbl_accounting","[type] =[charge]"
And "[student_id] = [employeeid]")

It comes back with a grand total of all the records in the
table and doesn't limit the selection based on the TYPE or
the EMPLOYEE.

If I remove the AND part of the statement, it works. It
comes back with a total of all the records in the table
that have a TYPE = to the TYPE on the form. But I want to
also limit the selection of records to the employee who is
displayed on the form. I guess my syntax is wrong. Anyone
know what is wrong with it?

Thanks

Your syntax, for one thing, is incorrect.
The AND is outside the quotes. I must be inside the quotes.

Also, you have [Student_ID] = [EmployeeID]. Is that correct (Student
field = Employee field)?
Also, the exact placement of quotes (and whether to use Single or
Double quotes) is dependent upon the datatype of the criteria fields.
So... what is the datatype of [Type] and what is the datatype of
[StudentID]? What is [Charge]? Is that a field on your form, or is
that supposed to be "Charge" (text)? Same thing with [EmployeeID]. Is
that a control on the form? Is it a number?

Let's say that [Type] is Text Datatype, and [StudentID] is Number and
[Charge] and [EmployeeID] are control names on your form.
Use:
=DSum("[amount]","Tbl_accounting","[type] = '" & Me![charge] & "'
And [student_id] = " & Me![employeeid])

Access will process the where clause similar to this:
=DSum("[amount]","Tbl_accounting","[Type] = 'SomeType' AND
[Student_id] = 185")

 

[connieharper]

Thanks for your reply. I tried your example but the field
just shows #NAME.

You asked some questions.

comparing student_id and employee_id is correct

In the tbl_accounting table, the charge field is text, the
student_id field is a number. employee_id is a number
field on the form.

I want to get a total of all records in the
tbl_accounting table that have a student_id that matches
the employee_id on the form and that have the
word "Charge" for the type.

Is this statement still correct to do that?

=DSum("[amount]","Tbl_accounting","[type] = '" & Me!
[charge] & "' And [student_id] = " & Me![employeeid])


Thanks.

-----Original Message-----

I have a form that contains a calculated field. I want it
to display a total for all records in the tbl_accounting
table for the employee that is displayed on the form and
for the type displayed on the form. I have used the
following dsum statement in the controlsource property of
this field:

=DSum("[amount]","Tbl_accounting","[type] =[charge]"
And "[student_id] = [employeeid]")

It comes back with a grand total of all the records in the
table and doesn't limit the selection based on the TYPE or
the EMPLOYEE.

If I remove the AND part of the statement, it works. It
comes back with a total of all the records in the table
that have a TYPE = to the TYPE on the form. But I want to
also limit the selection of records to the employee who is
displayed on the form. I guess my syntax is wrong. Anyone
know what is wrong with it?

Thanks

Your syntax, for one thing, is incorrect.
The AND is outside the quotes. I must be inside the quotes.

Also, you have [Student_ID] = [EmployeeID]. Is that correct (Student
field = Employee field)?
Also, the exact placement of quotes (and whether to use Single or
Double quotes) is dependent upon the datatype of the criteria fields.
So... what is the datatype of [Type] and what is the datatype of
[StudentID]? What is [Charge]? Is that a field on your form, or is
that supposed to be "Charge" (text)? Same thing with [EmployeeID]. Is
that a control on the form? Is it a number?

Let's say that [Type] is Text Datatype, and [StudentID] is Number and
[Charge] and [EmployeeID] are control names on your form.
Use:
=DSum("[amount]","Tbl_accounting","[type] = '" & Me! [charge] & "'
And [student_id] = " & Me![employeeid])

Access will process the where clause similar to this:
=DSum("[amount]","Tbl_accounting","[Type] = 'SomeType' AND
[Student_id] = 185")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

 

[Guest]

Connie
PMFJI........

Thanks for your reply. I tried your example but the field
just shows #NAME

That is because DSUM() is looking for a control on a form that is named charge.

tbl_accounting table that have a student_id that matches
the employee_id on the form and that have the
word "Charge" for the type

Is this statement still correct to do that

No. tr

=DSum("[amount]","Tbl_accounting","[type] = 'charge' And [student_id] = " & Me![employeeid]

Note that since charge is hard coded in the DSUM(), if you want to sum where [type] = 'cash' you will need another control or you could make an unbound combo box on the form to select the different payment options and use the DSUM() that fredg provided (but change [charge] to the name of the combo box)

You should also start using a naming convention when creating fields; see http://www.mvps.org/access/general/gen0012.ht

This helps prevent using reserved words for field names (Access gets confused) and identifies types - ie

use [txtType] instead of [type] for text fields, [curAmount] - currency type, [lngStudent_Id] if student_id is a long integer or [intStudent_Id] if it is an integer

Other good practices are: don't use spaces when naming objects and use proper case (aka camel back - like [curAmount])
....makes it easier to find mistakes and errors

HT
Stev

 

[fredg]

Thanks for your reply. I tried your example but the field
just shows #NAME.

You asked some questions.

comparing student_id and employee_id is correct

In the tbl_accounting table, the charge field is text, the
student_id field is a number. employee_id is a number
field on the form.

Regarding (from the above paragraph):

In the tbl_accounting table, the charge field is text,

Your next paragraph says you want to total records in which the [Type]
field contains the WORD 'Charge'. Is the field name that contains the
word "Charge" [Type] or is there another field or control that
contains the word "Charge" named [Charge]?

I want to get a total of all records in the
tbl_accounting table that have a student_id that matches
the employee_id on the form and that have the
word "Charge" for the type.

Is this statement still correct to do that?

=DSum("[amount]","Tbl_accounting","[type] = '" & Me!
[charge] & "' And [student_id] = " & Me![employeeid])

Thanks.

No it is NOT correct.
If the [Type] field is to return a word 'Charge', you must not use
brackets around the word, but single quotes, which means you must
change the placement of the quotes and single quotes needed.

=DSum("[amount]","Tbl_accounting","[type] = 'charge' And
[student_id] = " & Me![employeeid])

Your #Name error is probably because Access can't find the field named
[Charge] used in the previous code.
Also make sure the name of this control is not the same as the name of
any field used in the control expression.