adding calculation to form

[BHILO]

I am trying to add the differnce of two quieries with summary amounts into the footer of a form. I have inserted a text box into the footer and under properities control source have built the expression starting with = and bringing in each total field from 2 different quieries and get #name? What am I doing wrong?

 

[SoCalAccessPro]

Can you post your expression ?



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[MajP]

You are probably going to have to use a database lookup function such as dlookup to pull the values from the two queries. You can not directly reference a query value even if the query returns a single value.

 

[TheAceMan1]

How are ya BHILO . . .

A look at the [blue]SQL[/blue] of the two queries would be a big help as well . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[BHILO]

the expression is:

=[Deposits Query]![Sum Of AMT]- [PURCHASES Query]![Sum Of Amt]

 

[jamaarneen]

Hi BHILO

maybe you can try, to make sure that both fields are added to your form's "row source".

Ja

 

[SoCalAccessPro]

You are probably going to have to use a database lookup function such as dlookup to pull the values from the two queries. You can not directly reference a query value even if the query returns a single value.

This.

Use DLookup, DSum or a user defined function.

Here is how I do the same thing.

I have function (This could have been Dsum, BTW)

Public Function Sold()
Dim sql As String
Dim rst As New ADODB.Recordset

sql = "SELECT Sum(tblTrans.SellPrice) AS SumOfSellPrice FROM tblTrans"
rst.Open sql, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
Sold = rst.Fields(0)
End Function

And on my form, I have the following:

Private Sub Form_Open(Cancel As Integer)
Me.txtSold = Sold
End Sub

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com