Help with DSUM syntax in a Textbox

[Guest]

Using Office 2003 and Windows XP;

I'm trying to enter a DSUM formula into the Control Source of a text box on
a form; but I am entering it wrong and it doesn't produce any value at all;
I'm sure it's just a syntax error on my part. Here is what I have:

=DSum("[ACTUAL_HC]","[tblHeadCounts]","[DEPT1]=cboMainDept1.Value AND
[DEPT2]=cboMainDept2.Value AND [EMP_STATUS_ORIG] ='REG-FT'")

Where:
cboMainDept1 = a combobox on the current form;
cboMainDept2 = a combobox on the current form;
EMP_STATUS_ORIG = a column name in the table tblHeadCounts

Could someone please help me out in correcting my syntax? Thanks much in
advance.

 

[strive4peace]

you need to take the control references out of the quoted string in your
condition and delimit them if they are text or dates...

=DSum("[ACTUAL_HC]","[tblHeadCounts]","[DEPT1]= '" & cboMainDept1 & "'
AND [DEPT2]= '" & cboMainDept2 & "' AND [EMP_STATUS_ORIG] ='REG-FT'")

I have assumed that Dept1 and Dept2 are both text

btw, it appears that your database is not normalized. Whenever I see
fieldnames with a number after them, I suspect this.

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*