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
View RepliesI 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:
Code:
Me.txtTotalEarnings = DSum("Price", "KatiesPeriodTakings", "AppDate" >= CDate([Forms]! _&
[frmKatiesTakings]![txtStartDate]) And "AppDate" _&
<= CDate([Forms]![frmKatiesTakings]![txtEndDate]))
But I keep getting a mismatch error
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
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
=DSum("SoldAtPrice*Quantity","[Order Details]","IDNewOrders=" & [OrderID FK]).
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])
[Code] ....
Mycode works great when I use the DCount function, but fails when I change it to DSum which errors with: Error 94 - invalid use of null.
dblCntr = DSum("[intEventCount]", "tblResourceEvents", "[ResourceEventTypeID] = " & myKey & " AND [TimeFrameID] = " & Me.cbo1)
The entire table has valid data - no nulls. myKey and Me.cbo1 both have correct values.
Just a quick one,
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.
Thanks.
I have a form based on a lengthy SQL query.
I have several controls in this form whose control Source has the following formula:
=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)
When I navigate from one record to another record in this form, the values in the controls take a looooong time to populate (most of the time they come up as blank and when I move my mouse over that control - the value gets populated....this is slow !!!).
I believe this is happenning because I am using DSUM function all over the form.
What is the fastest way to display this control WITHOUT using DSUM?
Many thanks!
How is the collection of a sum of money to a particular substance.There is, for example, in the table named baby milk product.How can I collect the amounts of such material only.I used a DSum
View 6 Replies View Related I have a subform from which I want to calculate values into a text box on the main form, to keep a running total of weeks for individuals. Using the expression builder to just add the fields, I only get the total for the current sub-form record. If another record is added to the subform, the total reverts to zero, and then it takes that record's input as the total. Which makes logical sense.
I think I need to do a 'DSum' from the subform/table, but I'm not sure how to sum for just the current ID/individual. I have tried to bodge it myself with the expression builder, but it tells me that 'the function contains the wrong number of arguments'.
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
I want to add to values based on if yes or no is selected in an option group.
My thinking is to use DSUM. When 'yes' is selected I want to grab a value from one field and add it to a value in another field giving a running total. I am currently using the following code but it throws back an error. Is DSUM the way to go?
Case 1
txtTotal.Value = DSum("[s1]", "[s2]")
Hi there guys, ill keep it short and snappy,
Im trying to use the Dsum() function to add together the "Quantities" of specific products bought in transactions i.e. the total quantity for a product called "red t-shirt" and the total quantity for another product called "blue t-shirt". the following was the code i tried to use but it just adds together all quantities of all products:
DSum("[Quantity]","Query2","[Product Name] =[Product Name]")
Does anyone have any ideas at all please?
Thanks
Ok, I have a commercial database over which I have no control. It is ticket sales and I want to view them over time...
This is my table of transactions:
master_tix_header
tix_header_year
tix_header_performance_code
tix_header_no_seats - The number of seats sold
tix_header_first_transaction_date - The date of the transaction
The unique ID of the performance is made up of tix_header_year and tix_header_performance_code:
E.g.
In tix_header_year of 2004 there is a tix_header_performance_code of JSVH01 and in 2005 there is also JSVH01 - but there is never the same tix_header_performance_code more than once in the same tix_header_year...
Now I can get the number of tickets sold on each day using Sum() for a specific performance using:
SELECT SUM(tix_header_no_seats) AS TotalSeats, tix_header_first_transaction_date
FROM master_tix_header WHERE
tix_header_year = "2005"
AND tix_header_performance_code - "JSVH01"
GROUP BY tix_header_first_transaction_date;
This gives me:
TotalSeats tix_header_first_transaction_date
2 14/03/05
9 18/03/05
1 22/03/05
But what I want is:
TotalSeats tix_header_first_transaction_date
2 14/03/05
11 18/03/05
12 22/03/05
Can anyone give me any pointers?
I tried http://support.microsoft.com/kb/138911/ but it returned ambiguous results (seemed to reset the running total at the begining of each year...) or would say the query was too complex to run - I;ve probably missed something simple as I a newbie to the world of access/sql
TIA
Nick
PS: Access 97 if that makes any difference!
I am trying to add up the total amnt under a certain location # under a certain representative #. What im having the query do it pull information from several different tables including the rep # and that rep #'s loc #. I want to find the sum of the sales under the location # for that rrep #. So it will look it up based on the loc # after it finds the rep #'s loc #. What would be the best way to do that.
View 1 Replies View RelatedHi!
Can someone tell me why the following querie doesn't work?
txtcharges = DSum("[Charges]", "tblcharges", "[DateCharges] >= '" & startdate & "' AND [dateCharges] <= '" & enddate & "'") '
Thanks
JonyBravo
Hi,
I have a table that contains Product, Manufacturer, Date and Cost and want to have a field that sums up totals based on criteria, for example, total cost for each manufacturer over a set period.
I think I need to use a dsum, but although I've tried to structure this query, I'm getting nowhere fast!
Any help is appreciated!!
P.s. I have no sql expereince, so please can any response not include a brilliant, but greek-to-me sql! :o
What do I do if wanting to sum my data in a table if it is text in the field that groups them together. In the examples, the grouping is by productID, but my grouping is by a text field.
DSum("[Amount Aus]","Costs","[Costs.Area]=" & [Costs.Area] And "[Costs.Project Code]=" & [Costs.Project Code]")
The Costs.Project Code is the text field. The Costs.Area part works fine, but not Costs.Project Code
Any help would be great
Hi Guys,
Look, I have a table to record supplier invoices for vehicle maintenance services.
The fields at this table are:
1. [Vehicle_License_Plate] - text - this is to input the vehicle ID, or registration
2. [Supplier_ID] - text - this is to input the supplier ID, or supplier code
3. [Invoice] - text - this is to input the supplier invoice number
4. [Invoice_Date] - date/time - this is to input the invoice date
5. [Service] - text - this is to input the type of service, from a pre-determined list
6. [Amount] - number - this is to input the invoice amount
7. [Accepted] - yes/no - this is to input whether the invoice has been accepted by our services or not
8. [Accounting] - date/time - this is to input the date when invoice has been booked in the accounting system
Furthermore, at my main vehicle table (the one where all the information respective to a vehicle is recorded), I have some fields where I need to have the total amount spent per service type (one field for each type of service).
In my intention to have an update query calculating the values for these fields, based on the supplier invoices table, I'm having a problem...
At the update query, I'm saying that I want to update i. e. field [Cost_Transportation], on my main vehicle table to the result of the following formula:
DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA'")
Problem is that, instead of having the total "Transportation" amount for the each vehicle well calculated and updated at the main vehicle database field, I'm getting the overall total transportation amount from the supplier invoices table, and this total amount is then updated on all [Cost_Transportation] fields at my main vehicle table.
Istead, what I would need was a formula that could calculate, from the "DB_Supplier_Invoices" table, the total [Amount] per service AND per vehicle, and then update the fields at the main vehicle table accordingly.
Any help will be highly appreciated!!
Should you require further clarification, please advise.
Many thanks and best regards.
hi
i have a access database (250mb)
when access run this line,
i get:
run time error : 6
Overflow
totaldga = DSum("nombre_dga", "tbExcel", " jour >= " + "#" & Me.txtDateDebut & "#" + " and jour <= " + "#" & Me.txtDateFin & "#" + " and heure_debut >= " + "#" & Me.txtHeureDebut & "#" + " and heure_fin <= " + "#" & Me.txtHeureFin & "#" + " and transit in (" & Left(strIN, Len(strIN) - 1) & ")")
if i do:
totaldga = DSum("nombre_dga", "tbExcel", " jour >= " + "#" & Me.txtDateDebut & "#" + " and heure_debut >= " + "#" & Me.txtHeureDebut & "#" + " and heure_fin <= " + "#" & Me.txtHeureFin & "#" + " and transit in (" & Left(strIN, Len(strIN) - 1) & ")")
that work fine........ access don't seem to like: jour >=....... and jour <= ........
any idea?
thanks
Hi
I am trying to use DSUM to calculate a running total for an outstanding balance for each period over a number of years. The problem I have is it is not totalling when the year changes, it starts a new running sum for the next year. For each year, there are 12 periods. Is there anyway I can get it to total for all years? My DSUM calculation is
RunningSum: Val(DSum("([CHG_AMT]-[CASH_AMT])","RUNNING","[FCYEAR_ID] = '" & [FCYEAR_ID] & "' and [PERIOD_NO] <= " & [AMonth]))
Thanks in advance for any help
Alison
Is there a way to add to the below code (a Field in a saved Query) that if the RemitDate is the same as the RemitDate and SOInvoiceNumber is the same the SOInvoiceNumber, that the DSum function will work. And also still work as the code reads.
PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCustomerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate],"mm/dd/yyyy") & "#"),0),"$#,##0.00")
I have attached a view of what I’m trying to accomplish, I apologize for the crudeness of the Form it’s still under construction.
The following statement returns NULL
Dim str As String
Dim n As Variant
str = "RemitDate >= #" & Forms![Report Menu].FromDate & "# AND RemitDate <= #" & Forms![Report Menu].ToDate & "#"
n = DSum("GSTPaid", "queryJobRemittance", str)
yet however when I run the query ie
SELECT sum(GSTPaid) FROM queryjobremittance
WHERE remitdate>=[Forms]![report menu].[fromDate] And remitdate<=[forms]![report menu].[todate];
it works fine.
The remitdate is short date,the format is dd/mm/yy, and the dsum works fine when no criteria.
Any advice will be much appreciated. Thanks.
Is there a way to join tables when using DSum in an expression on a control box?
I need to do the equivaent of:
SELECT SUM(ColumnA)
FROM Table1
INNER JOIN Table2 on Table1.AreaID = Table2.AreaID
AND Table2.AreaDescription = 'UK'
...but in an expression. I can't use the query above because the form has already used a different query as it's source. So, something like:
=DSum("[ColumnA]","Table1","[Table2.][AreaID]=") ???
Or is there another way round this?
I guess I could add the Table2.Description to all of the tables, but that kind of defeats the point of a relationa database doesn't it ?
Hi
Could someone help with showing me how to sync a DSUM to a record as i am currently summing all the records instead of the ones i need?
Thanks
Hi,
I have been reading through the boards and managed to get this far but I have been stuck on the same problem for hours, so I am now asking for help.
I need to create a YTD running total:
Fields:
Period, Units
01/01/2006, 10
01/02/2006, 20
UnitsYTD: DSum("[Units]","tblAdjControlLog_YTD","[Period] <=#" & [Period] & "#")
I have managed to get to this point and now seem to be stumped after several hours of trying different approaches.
The query keeps returning the following results:
Period, Units, UnitsYTD
01/01/2006, 10, 60
01/02/2006, 20, 60
01/03/2006, 30, 60
Instead of:
Period, Units, UnitsYTD
01/01/2006, 10, 10
01/02/2006, 20, 30
01/03/2006, 30, 60
Thanks in advance
Daz......
I understand that it is not good practice to store calculated values in a table, but I am having a difficult time doing otherwise. I have a form with the field "volume". I have a table that stores monthly volumes for each product type. In my AfterUpdate of my "effectivemonth" field I have a DSum query go to the Volumes table and sum the volumes for the particular product based on the effective month. Each month all of the volumes change and I have to go into every record via my form one at a time and trigger the AfterUpdate event in order to get the field updated with the new volumes.
These volumes 1) have to be visible on my form at all times and 2) are used as the basis for most of my queries, pulling from the "volume" field in my table.
I am looking for a way to update the entire database volumes with one command button. Is this possible? I've thrown in my current AfterUpdate code to give you an idea of what I am doing now.
If Not IsNull(Me.fcstloc) Then
Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
Me.prodvolume = DLookup("[volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "")
If Me!otherloc = "Supplies forecast" Then
Me.prodvolume = DSum("[volume]", "qryMonthlySupplies", "[product] = '" & Forms!Projects!currentpn & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
End If
End If
Any ideas would be helpful.
Thanks