Help Please - Using DSum in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Mornin

In my Access forms I am attempting to have Access software do a DSum from a table. Everything works fine as long as the DSum criteria is an actual number

This is the expression that the Wizard helped me create from within Access forms. I can only make this work as long as the account number is 123 and not a form field

=DSum("[Approved Expenses]!Cost","[Approved Expenses]","[Apporved Expenses]!Account="'123'"

where Approved Expenses is a table, Cost is the field I want to sum up when the Account field in the approved expenses table is the same as the account field in the Access form I am working with

However I need to make the DSum criteria (currently = 123) a variable which is a field that changes with every record in the form I am working on. The examples Microsoft provides are illustrated below. However how do I replace UK with a field in the form I am working with

DSum(expr, domain, [criteria]

DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
 
Try:
=DSum("Cost", "[Approved Expenses]", "Account = " & [tx])

If Account is a Text field (not a Number field), you need extra quotes:
=DSum("Cost", "[Approved Expenses]", "Account = """ & [tx] & """")

These examples assume the account value is in a text box named "tx".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Leo said:
Good Morning

In my Access forms I am attempting to have Access software do a DSum from
Click to expand...
a table. Everything works fine as long as the DSum criteria is an actual
number.
This is the expression that the Wizard helped me create from within Access
Click to expand...
forms. I can only make this work as long as the account number is 123 and
not a form field.
=DSum("[Approved Expenses]!Cost","[Approved Expenses]","[Apporved Expenses]!Account="'123'")

where Approved Expenses is a table, Cost is the field I want to sum up
Click to expand...
when the Account field in the approved expenses table is the same as the
account field in the Access form I am working with.
However I need to make the DSum criteria (currently = 123) a variable
Click to expand...
which is a field that changes with every record in the form I am working on.
The examples Microsoft provides are illustrated below. However how do I
replace UK with a field in the form I am working with?
DSum(expr, domain, [criteria])

DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
Click to expand...
 
Hi,

If the textbox control on the Form holds a numeric value it should be

=DSum("[Cost]","[Approved Expenses]","[Account]= Forms![Form Name]![Control
on Form] " )

If it's a text value then it's

=DSum("[Cost]","[Approved Expenses]","[Account]= ' " & Forms![Form
Name]![Control on Form] & " ' " )

note: only need the table name for the Domain

See the following article for tips:
ACC2000: DLookup() Usage, Examples, and Troubleshooting
http://support.microsoft.com/default.aspx?scid=kb;en-us;208786


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Thread-Topic: Help Please - Using DSum in a form
| thread-index: AcP4arQb9XWQlugtQXSvMIvOZbCR5w==
| X-Tomcat-NG: microsoft.public.access.forms
| From: =?Utf-8?B?TGVv?= <[email protected]>
| Subject: Help Please - Using DSum in a form
| Date: Sat, 21 Feb 2004 03:06:06 -0800
| Lines: 15
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.forms
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:256084
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.access.forms
|
| Good Morning

In my Access forms I am attempting to have Access software do a DSum from a
table. Everything works fine as long as the DSum criteria is an actual
number.

This is the expression that the Wizard helped me create from within Access
forms. I can only make this work as long as the account number is 123 and
not a form field.

=DSum("[Approved Expenses]!Cost","[Approved Expenses]","[Apporved
Expenses]!Account="'123'")

where Approved Expenses is a table, Cost is the field I want to sum up when
the Account field in the approved expenses table is the same as the account
field in the Access form I am working with.

However I need to make the DSum criteria (currently = 123) a variable which
is a field that changes with every record in the form I am working on. The
examples Microsoft provides are illustrated below. However how do I
replace UK with a field in the form I am working with?

DSum(expr, domain, [criteria])

DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
|
 
Back
Top