MS-Access 2003 - Expression in a Text Box on a form

Question

just wondering when using an expression on a form in a text box, to return a value from a table, can the expression have multiple tables in the expression to return the value?

the tables are linked and I can return the value in a query, so I figured that Access would be able to do it with this method as well????

=DSum("[tblMain]![Revenue]","tblMain","[tblMain]![Quarter]=3 AND [tblMain]![Region]='NorthEast'" AND [tblOffice]![Location]='NewYork'")

this is the expression that I entered into my text box, without the reference to the 2nd table it works fine, but once I had it, I get the flickering error message in the text box (just as on a report)......

I know this method is probably used more in reports than forms, but I am novice, and trying to come up with a dashboard solution that returns lots of facts quickly per department. I am using this in the "Control Source" field of the data tab of the properties window, not VB. Mainly because I do not know how to get it to work with VB.

Thanks for the help as always!

Answer 1

Hi Justin,

How are these tables related? Can you describe the relationship and any primary/foreign keys?

Also, referencing the table name is not necessary in the first parameter of this function (since it is already taken care of in the second one).

For example, your code could be:

=DSum("Revenue","tblMain","Quarter=3 AND Region='NorthEast'" AND [tblOffice]![Location]='NewYork'")

Just trying to save you some keystrokes and increase its readability. :)

Answer 2

As far as I know, you cannot refer to more than one table or query in a domain aggregate function. As grazird says, how are these tables related? Let us say it is on tblMain ID, you can build a query called, say, qryMainOffice, the SQL (SQL View, Query Design window) would look something like:

SELECT [tblMain].[Revenue],[tblMain]![Quarter],[tblMain]![Region],
       [tblOffice]![Location]
FROM tblMain 
INNER JOIN tblOffice 
ON tblMain.ID = tblOffice.MainID

DSum would then be (remove line break):

=NZ(DSum("[Revenue]","qryMainOffice",
 "[Quarter]=3 AND [Region]='NorthEast' AND [Location]='NewYork'"),"Not found")

You could also use a recordset or query in VBA to return the value.

EDIT re COMMENT

To use the above in VBA, you either need to add parameters or use a string:

''Reference: Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL as String

Set db= CurrentDB

strSQL = "SELECT Sum(t.[Revenue]) As TotalNY" _
       & "FROM tblMain t " _
       & "INNER JOIN tblOffice o " _
       & "ON t.ID = o.MainID " _
       & "WHERE t.[Quarter]=3 AND t.[Region]='NorthEast' " _
       & "AND o.[Location]='NewYork' " _

 '' I have use aliases for simplicity, t-tblMain, o-tblOffice
 '' If you wish to reference a control, use the value, like so: 
 '' & " AND [Location]='" & Me.txtCity & "'"
 '' Dates should be formated to year, month, day
 '' For joins, see http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

 Set rs = db.OpenRecordset strSQL

 If Not rs.EOF Then
    Me.txtAnswer = rs!TotNY
 Else
    Me.txtAnswer = "N/A"
 End If

You can also use different queries to return several results that can be shown with a list box or a subform:

strSQL = "SELECT TOP 5 o.[Location]," _
       & "Sum(t.[Revenue]) AS TotRevenue" _
       & "FROM tblMain t " _
       & "INNER JOIN tblOffice o " _
       & "ON t.ID = o.MainID " _
       & "WHERE t.[Quarter]=3 AND t.[Region]='NorthEast' " _
       & "GROUP BY o.[Location]"

The above would return revenue for quarter 3 for all locations in NorthEast region. If you want the top values of each group, you are looking at a more complicated query, which I will leave for now.