Re: DSUM syntax problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: connieharper (anonymous_at_discussions.microsoft.com)
Date: 03/28/04 

Date: Sun, 28 Mar 2004 10:53:48 -0800

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-----
>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 tbl_accounting ... > the EMPLOYEE. ... The AND is outside the quotes. ... What is [Charge]? ...
    (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: Question on displaying repeating data
    ... Next, I have to choose the link from the main form, to the subform. ... >> of historical quotes. ... The query logic is relatively simple. ... >> My problem is that I don't know how to display this. ...
    (microsoft.public.access.forms)