 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008Runtime Error 3421
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 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) [quoted text clipped - 29 lines] > Thanks in Advance, > Paul 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. [quoted text clipped - 42 lines] > > Thanks in Advance, > > Paul 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 > Hello Jeanette, > Thanks for your answer. [quoted text clipped - 52 lines] >> > Thanks in Advance, >> > Paul 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 > Paul, > here is an example using DLookup [quoted text clipped - 72 lines] > >> > Thanks in Advance, > >> > Paul 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 > Jeanette, > I couldn't get that to work, but I tried DSum in the following way: [quoted text clipped - 90 lines] >> >> > Thanks in Advance, >> >> > Paul
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.