Need to display calculation in field on a form

[TinaCa]

I have create a query that gives me a total of the amount that will be billed (on either the 1st or 15th) of the month. This amount needs to populate into a text box on the form after the user selects the date they want to do the billing for (1 or 15). Concept is... tell me how much i will bill if I create the invoices today. So the steps are:

User selects a billing date (either 1 or 15) from a combo on the form. I was running this code:

Dim TtlBl As String
Dim BillDt As Integer

BillDt = Me.cboPrntInv



'DoCmd.OpenQuery "qryGetBillTotals"

'TtlBl = "SELECT tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate, " _
'& "Sum(([ExtraCost]*[ExtrasQty])+[CompanyContractAmt]) AS ExtraTotal " _
'& "FROM CompanyInformation LEFT JOIN tblCompanyExtras ON " _
'& "CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId " _
'& "GROUP BY tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate " _
'& "((CompanyInformation.CompanyBillingDate)= " & BillDt & "));"


Me.txtTotBld.RowSource = TtlBl

But the field was not updating. I tried setting the recordsource or control source to both the query and the select statement but I'm still not getting the total to display in the field.

Here is the query:

SELECT Sum(([ExtraCost]*[ExtrasQty])+[CompanyContractAmt]) AS ExtraTotal
FROM CompanyInformation LEFT JOIN tblCompanyExtras ON CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId
WHERE (((tblCompanyExtras.DateBilled) Is Null) AND ((CompanyInformation.CompanyBillingDate)=[Forms]![frmCreateInvoices]![cboPrntInv]));

Thanks, Tina

[TinaCa]

I have create a query that gives me a total of the amount that will be billed (on either the 1st or 15th) of the month. This amount needs to populate into a text box on the form after the user selects the date they want to do the billing for (1 or 15). Concept is... tell me how much i will bill if I create the invoices today. So the steps are:

User selects a billing date (either 1 or 15) from a combo on the form. I was running this code:

Dim TtlBl As String
Dim BillDt As Integer

BillDt = Me.cboPrntInv

'DoCmd.OpenQuery "qryGetBillTotals"

'TtlBl = "SELECT tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate, " _
'& "Sum(([ExtraCost]*[ExtrasQty])+[CompanyContractAmt]) AS ExtraTotal " _
'& "FROM CompanyInformation LEFT JOIN tblCompanyExtras ON " _
'& "CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId " _
'& "GROUP BY tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate " _
'& "((CompanyInformation.CompanyBillingDate)= " & BillDt & "));"


Me.txtTotBld.RowSource = TtlBl

But the field was not updating. I tried setting the recordsource or control source to both the query and the select statement but I'm still not getting the total to display in the field.

Here is the query:

SELECT Sum(([ExtraCost]*[ExtrasQty])+[CompanyContractAmt]) AS ExtraTotal
FROM CompanyInformation LEFT JOIN tblCompanyExtras ON CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId
WHERE (((tblCompanyExtras.DateBilled) Is Null) AND ((CompanyInformation.CompanyBillingDate)=[Forms]![frmCreateInvoices]![cboPrntInv]));

Thanks, Tina

I made it too complicated. Instead of above, I created a query for the ControlSource (based on the code above) of the field and after a user clicks the combo I added a Refresh to the Afterupdate event.