How did you enter it?  Try:

=DSum("[Total Value]","[qry_SubContract_ChargeCode_Values]","[SubContract_ID]=" & [SubContract_ID])

I just re-read the question and I think you are trying to subtotal the subcontract by Prime Contract.  Correct?

I need to rethink this one when I'm not half asleep.

What data type is SubContract_ID? Number or Text?

Scott,

That worked.  Thank you!

The above expression worked but now I can't get it to display as currency.  I'm unable to select a format for the Text Box on the Form Property sheet.

Try using CCur to convert it to currency
CCur(stuff)

Formats in queries can sometimes be finicky.  
In your query where you have the DSum surround it with the Format function.

=Format(DSum("[Total Value]","[qry_SubContract_ChargeCode_Values]","[SubContract_ID]=" & [SubContract_ID]), "Currency")

Sometimes format in a query won't "stick".
You will probably have to format the display field in your form.  Is it a continuous form or a single form?

Scott,

That worked great.

I now need to limit it based off another column [PoP End].  It needs to only use values when [PoP End] is >=Date()

Sounds good to me.  Just add it to your query.

Scott,

I was having issues with doing everything in the same query.  I built a new query and I'm grabbing the values from [tbl_ChargeCodes] instead of another query.  my current expression without the Date() criteria is:

Cumulative Invoiced: Format(DSum("[Cumulative Invoiced]","[tbl_ChargeCodes]","[SubContract_ID]=" & [SubContracts_ID]), "Currency")

This expression works, but I need to not include the values from records who's [PoP End] has passed.

I'm using the following expression but it's returning #Error:

Cumulative Invoiced: Format(DSum("[Cumulative Invoiced]","[tbl_ChargeCodes]","[SubContract_ID]=" & [SubContracts_ID] & "' AND [tbl_ChargeCodes].[PoP End]>=" & Date()), "Currency")

Walt -
The  [tbl_ChargeCodes].[PoP End]>=" & Date() should be in the query criteria.  Not in the formula for summing the subcontracts.
If that doesn't make sense take a screen shot of your query in design mode and post it using the Upload Images in the first post of this thread.

Scott,

My brain wasn't thinking the correct way.  That works.  I really appreciate the assistance.

You get to where you can't see the forest for the trees some times.  The more you fix things the quicker you see it in the future.

Glad that helped.  

I've encountered another issue with this.

I'm on a form with comboboxes for:

Major Command
Component Command
Command

I've created text boxes beside each of these to display the Total Value, Funded Amount, Invoiced Amount, and Burn Rate.

I'm using the following example as the expression for the Total Value beside Major Command:

=Format(Nz(DSum("[Total Value]","[qry_Major Command Values]","[Major Command_ID]=" & [Combo33]),0),"Currency")

I have #Error in each field prior to selecting something in the Combo Box.  Once I make a selection everything works as desired.  I that the Nz() would fix that?

That error is because your telling it to search for a value, and then not even giving it the value to search for (the combo box selection). NZ is for if there is a value, but it is blank/null, then it will let you put a value in. You'll need to use an if then statement or IIF on this too.

Alternatively, you could just throw the code that sets the fields into the after update event of the combo box. Then they'd be blank when you don't have a choice, and when you change the combo box it would update.

Oh wow I wish I followed this one from the begining
if posible I like to see the end product, I work for the Goverment and do this all the time on service contracts.  IDIQ and JOC.
Always can use a fresh look on how things are done.

V/r
Juan

Adam,

That worked but it created a new problem.  When I filter something that doesn't have an associated record I the code throws an error for my [Burn Rate] text box.  the code I'm using is:

Text57.Value = IIf([Text56] = 0, 0, [Text56] / [Text55])

One thing after another :). If you're using that in an event try this

If Text56 = 0 or Text56 = Null or Text56 = "" Then
Text57 = 0
Else
Text57 = Text56/Text55
End If

Adam,

Thanks again.  I ended up having to put the following in the Control Source for the Text box for it to work:

=IIf([Text45]=0 Or [Text47]=0 Or [Text49]=0,0,[Text49]/[Text47])

I hope these textboxes are getting descriptive names soon :)

You go Alex!
FWIW - I do wish it was easier to do it when you are adding it to the forms in the first place.

I can't blame you if the teacher doesn't :p

Alex you still can't blame me. :-) Not my code.
That's one of my peeves too.  Just a PITA to do some times.

Alex,

Yes.  I'll fix it soon.  I'm learning as I go.

Hey hey... don't yell at the teacher. The only thing I USED to say didn't need descriptive names was command buttons, and even then I turned around and changed my stance after Alex talked me into it.