Modules & VBA :: Using DSUM To Calculate Sum From Either A Table Or Query To Display On Form
Aug 24, 2013
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings
The field I want totalled is called Price
The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
i have an orders form with aorder details sub form.I need a grand total of the order that i need to store in my orders table. Cant seem to make anything to work is this becouse my form is a sub form .Basically i am doing a dsum quantity *sold at price with a where id information.Doi i have to do anything special as the info is in the sub forms table
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.
I have a form, with a sub-form, from which a call a datamacro to calculate totals(from the same table as the control source of the sub-form) and then want to display the totals on the parent form!! The RETURNVARS all have the correct total values, but i cant assign them to a control on the parent form!! none of the controls are recognized when i try and set the PROPERTY VALUE to the totals? (spellings are definitely correct)...
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
I have a TextBox in my form, that currently has this DSum in it: =DSum("QtyAdd","Orders","ProdID")-DSum("QtySub","Output","ProdID")
(ProdID is another TextBox in the form, QtyAdd is from the Orders table, and QtySub is from the Output table)
I'm also wanting to query it with more WHERE statements but finding I can only do this once with a DSum.
I'm wanting to effectively do this: =DSum("QtyAdd","Orders","ProdID AND StockItem = Yes AND Arrival = Yes")-DSum("QtySub","Output","ProdID AND Dispatch = Yes")
So, WHERE the field StockItem and Arrival = Yes in the Orders table and WHERE Dispatch = Yes in the Output table.
Does anyone know how I can achieve this?
I was thinking an SQL query but don't know how to display a Query into a TextBox.
I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;
First hundred should be calculated with 100.00 X 5.79 = 579.00 Second 200.00 X 8.11 = 1622.00 Third 93.00 X 12.33 = 1146.69
Total should be displayed in the amount field = 3347.69
I have created a database that records the scores of each player etc and puts them in league table like structure.
Within this database which I would like to extend I would like to know if its possible to have a query to calculate a league table for the teams if home team overal score is greater than away team home team get 2 points for win 1 for draw no points of loss and same if away team won.I also want to be able to change it to a web database and add club address details like contact list to it.
I have an Access 2009 report that displays the sums at the bottom of some columns. Is there a way to calculate and display the percentage one sum is of another?
I am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?
Every time i try to get info from a sub form or its table ,Things never sem to work.Is there something special you have to do for eg maybe going via the main form?I am trying to use dsum as an alternative to multiplying info in the sub form.This is the code
I want to replace some values in a TNS_QUERY with DSUM. The error message tells me I have to use an updateable table.
How can I avoid to create a query and replace the values directly in the original table TNS? I just group on the original table.
Code: Public Sub TNS_QUERY() Dim strSQL As String Dim x As Double Dim qdf As QueryDef strSQL = "SELECT TNS.[Division],TNS.[Customer_Split],SUM([TOTAL_NET_SALES])
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
I am looking to generate a total number of a given column based on two criteria.I would like a label (or textbox if necessary) to display a given total of "Active" devices based on a given month.The table name is "Blackberry" and the specific columns I would like to check would be "Activated?" and "Registration Date". I'm not sure if I should be using a dsum or dcount and the error i keep getting is a type mismatch.an active device would have the text value of "Yes.
Dim advalue As Integer advalue = DCount("[Activated?]", "Blackberry", "[Activated?]=yes" And "[Registration Date] > #01/01/2000#") lblad.Caption = advalue
I have a problem in which in the user may need to select 3 records at random from my table and display some details of those records in from view so that they can edit certain fields and just look at others.
Ideally I'd want a form with 3 separate dropdown boxes and each dropdown has the option to select 1 record from all the records in my table.
All 3 records must be visible on the screen at the same time.
I want to display the text from one combo in a text box in another form based on what's selected in a second combo box. The text box is in a different form from the combo boxes.
Sub D_ComponentTypeCmb_Change()
If Me.D_ComponentNameCmb.Value = "Customise" Then Forms!CustomComponentF!C_ComponentTxt.Value = Me.D_ComponentTypeCmb Else Forms!CustomComponentF!C_ComponentTxt.Value = "" End If End Sub
Access 2007: I have a continuous form which, when it opens, uses code to display alternating colors... I need to set a default 'alternate' colour, and I want to find the code to use for Light Grey - on the design of a form, the color to use on a control would be #903C39 but when I try to set that as my default colour in the code it doesn't like it! I can use vbRed, vbBlue, vbYellow, etc, and that works perfectly but I can't find a vbLightGrey
The user will select two dates. Start and End date. The user will hit the run button and the application will read an excel file with employee data and only display/output the records within that date range selected (employment date).
Code:
Option Compare Database Function DeleteTable() 'Delete old records from AllEmployeesData table On Error GoTo DeleteTable_Err
[code]....
I am not getting the query displayed on the datasheet after it is done executing. It's just a blank sheet. Also the output file is just this:
Code: ssn last mi first employ
I did a quick query test using SQL in Access and it worked fine there. I am guessing my error is somwhere in this section:
Code: '--Display query result on Datasheet and Output query to text file With db Set qdf = .CreateQueryDef("NewHireQuery", strSQL) DoCmd.OpenQuery "NewHireQuery" .QueryDefs.Delete "NewHireQuery" End With db.Close qdf.Close Set qdf = Nothing
