Re: DSUM syntax problem

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: fredg (fgutkind_at_example.invalid)
Date: 03/28/04 

Date: Sun, 28 Mar 2004 18:13:48 GMT

On Sun, 28 Mar 2004 07:45:20 -0800, connieharper wrote:

> 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.

Relevant Pages

  • Re: DSUM syntax problem
    ... >> to display a total for all records in the ... >> table for the employee that is displayed on the form ... >The AND is outside the quotes. ... >Double quotes) is dependent upon the datatype of the ...
    (microsoft.public.access.forms)
  • Re: Validating ComboBox Entries
    ... find that several hours have been charged to a now invalid project. ... The employee can work on the project up to and including the ... from the list display, since it has passed its expiry date. ... I have created my Combo Control as follows: ...
    (microsoft.public.access.formscoding)
  • Re: Populate a legacy textbox control using a legacy drop down con
    ... Sub c ... Case Doug ... How can I get it to display this info? ... Dim employee As Range ...
    (microsoft.public.word.vba.general)
  • Re: Populate a legacy textbox control using a legacy drop down con
    ... How can I get it to display this info? ... Dim SourceDoc As Document, myform As Document ... Dim employee As Range ... Set SourceDoc = Nothing ...
    (microsoft.public.word.vba.general)
  • Re: 2 Questions on Default Values and Multiple Field Lookup
    ... ancillary Employee or Company details. ... Since the EmpID is always static, a subform based on the EmpDetails ... would work well to "display" the Employee ancillary data values from ... In terms of the company look up, I realize we normally capture only the ...
    (microsoft.public.access.formscoding)