Paul,
yes, you use the DSum in a similar way to using DLookup.
Here are some examples from access vba help.
The following example totals the values from the Freight field for orders
shipped to the United Kingdom. The domain is an Orders table. The criteria
argument restricts the resulting set of records to those for which
ShipCountry equals UK.
Dim curX As Currency
curX = DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
The next example calculates a total by using two separate criteria. Note
that single quotation marks (') and number signs (#) are included in the
string expression, so that when the strings are concatenated, the string
literal will be enclosed in single quotation marks, and the date will be
enclosed in number signs.
Dim curX As Currency
curX = DSum("[Freight]", "Orders", _
"[ShipCountry] = 'UK' AND [ShippedDate] > #1-1-95#")
DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " _
& [ProductID])
When the query is run, Microsoft Access calculates the total amount of sales
for each product, based on information from an Order Details table. The sum
of sales for each product is added to the Products table.
Jeanette Cunningham -- Melbourne Victoria Australia
Paul3rd said:
Jeanette,
I couldn't get that to work, but I tried DSum in the following way:
Dim FRSum = String
FRSum = DSum("[FlatRate]" , "5/10/2008")
Me.Text42.Value = FRSum
And that worked fine.
I'd like to change the domain portion of the DSum expression to use the
value of a combo box on my form [cboApptDate].
DSum("[FlatRate]" , "insert_string_expression & [cboApptDate]")
is this possible?
Paul
Jeanette Cunningham said:
Paul,
here is an example using DLookup
strCriteria = "[ProductID] =" & Forms![Order Details]!ProductID)
Me.Text43 = DLookup("[ProductName]", "Products", strCriteria)
In the above, notice how strCriteria is written like a Where clause.
The where clause specifies the name and value of the field to match on.
In the above is is looking to find the matching record to the value of
ProductID on the form.
The value of ProductID on the form is in another text box called
ProductID.
You need to find which field uniquely identifies the last row in your
union
query.
If you can't find a unique identifier, consider constructing your query
to
include a unique identifier.
Jeanette Cunningham -- Melbourne Victoria Australia
Paul3rd said:
Hello Jeanette,
Thanks for your answer.
The SUM value of the FlatRate field is on the last row of the UNION
query,
how do I declare that in the strCriteria statement?
Dim strCriteria As String
strCriteria = "LookAtLastRow_of_Query"
Me.Text43 = DLookup("[FlatRate]", "5/10/2008_Query]", strCriteria)
Thanks,
Paul
:
Paul,
you can use a simple DLookup to put a value from a query into a text
box.
strCriteria is a string expression that selects the row you want from
the
query, based on values of other fields/controls on your form
Dim strCriteria As String
strCriteria = "[PrimaryKeyID] = 2"
Me.TextboxName = DLookup("[TheField]", "QueryName", strCriteria)
Jeanette Cunningham -- Melbourne Victoria Australia
Hello,
I have an unbound form and an unbound text box (Text43)
on the form.
I've created a UNION query (5/10/2008_Query) that works on it's
own, but when I try and use the SQL to populate the text box
I get the following error:
Runtime Error 3421
"Data Type Conversion Error"
This occurs on 'Set qd = db.QueryDefs("5/10/2008_Query")'
Private Sub Form_Current()
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim sq As String
Dim sVar As String
'On Error Resume Next
Set db = CurrentDb
Set qd = db.QueryDefs("5/10/2008_Query")
sq = "SELECT [5/10/2008].FlatRate From [5/10/2008]UNION ALL SELECT
Sum([5/10/2008].FlatRate) AS SumOfFlatRate From [5/10/2008];"
Set rs = qd.OpenRecordset("5/10/2008_Query")
sVar = rs!["SumOfFlatRate"]
Me.Text43.Value = sVar
rs.Close
End Sub
Does anyone have any ideas on how I can get this to work?
Thanks in Advance,
Paul
