Control updated via another control

[Bonz]

Hello Everyone,

This is my first time posting to the Access side of the board. I’ve been asked to modify someone else’s Access db and I keep getting #Error in a field on their form.

This is what I’ve placed in the “Control Source” for the control named “txtSumProjectHours” :

=Sum(IIf([txtProjectNumber]=[BillingInformation]![Project Number],[BillingInformation]![Hours Worked],0))

What I would like to do is in txtSumProjectHours show the sum of hours for the project indicated in txtProjectNumber.

I did not add the control via clicking “add existing fields” but by clicking on text box on the Design tab--> controls-->Text Box.

The “Record Source” for the form is the table named “BillingInformation” and all of the fields in the txtSumProjectHours “Control Source” are fields in the table.

The form is used to log when an employees works on a project: logging date, start time, finish time and so on. Several different employees can work on the same project so there can be several records in the BillingInformation table with the same “Project Number”.

I’m new to access and not sure if what I want is possible so any suggestions will be greatly appreciated.

Thank You!

 

[xenou]

Try a domain aggregate query DSUM():
=DSUM("[Hours Worked]","[Billing Information]","[Project Number] = " & [txtProjectNumber])

 

[Bonz]

Hi Xenou,

Thank you for the replay, I pasted your suggestion in the control source but still get #Error in the control in Form View.

Is ther any additional information that may help you to understand the problem?

 

[xenou]

Is project number a real number or a text data type?
Is there a project number on the form when you have the error?

Note that if DSum works (as I expect it to then this query should also work (on its own):

SELECT SUM([Hours Worked]) As SumOfHoursWorked
 FROM [Billing Information] 
WHERE [Project Number] = 1

I have assumed project number is a number data type.

 

[Bonz]

Xenou,

Project number is a text data type as the field contains alphanumeric date.
Yes, I receive #Error even when there is data in the txtProjectNumber control.

 

[xenou]

Project number is a text data type as the field contains alphanumeric date.

Okay.

If text:
=DSUM("[Hours Worked]","[Billing Information]","[Project Number] = '" & [txtProjectNumber] & "'")

If date:
=DSUM("[Hours Worked]","[Billing Information]","[Project Number] = #" & [txtProjectNumber] & "#")

In Access, text must be enclosed in single quotes in SQL. Dates must be enclosed in hashes. When you say alphanumeric text dates I'm not sure if that's really dates or text. So we try both.

 

[Bonz]

Thanks Xenou,

The text version worked perfectly, just had to name the table at "[BillingInformation]".

You saved me a ton of time---Thank You!