DSum With Date
Mar 16, 2006
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.
View Replies
ADVERTISEMENT
Mar 27, 2006
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......
View 1 Replies
View Related
Jan 16, 2014
I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
View 4 Replies
View Related
Oct 19, 2013
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
View 10 Replies
View Related
May 11, 2015
I've been struggling with the following expression:
=DSum("[FieldName]","TableName","[FieldName]>=" & [Forms]![FormName]![textbox01]& " and " &[FieldName]<= " & [Forms]![FormName]![textbox02] & "")
I keep getting invalid syntax (Access 2007). I tried adding the # symbols but no success.
I also need to put an additional criteria for a Integer field in the same expression where the value is True.
View 5 Replies
View Related
Jul 10, 2013
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
View 1 Replies
View Related
Sep 25, 2014
I m using Access 2010.I m Facing a problem in the undermentioned expression,
OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)
when i run the query it gives "Undefined Function 'Format' in Expression" ?
View 3 Replies
View Related
Jan 19, 2015
I am having difficulty getting a second textbox to display the correct total of hours based on a date entered into a first textbox.
Setup:
Table Name: TestTable1
Fields: 'RequestDate' & 'Hours'
Form: 'Form1'
2 Textboxes Unbound: Named 'Date' & 'Total'
What I am trying to accomplish:
Based upon a date entered into the "Date" textbox I want the "Total" textbox to display the total hours associated with that date.
What I have tried:
I have tried using, in the control source property of the "Total" textbox, many iterations of both Sum(IIF & DSum(
I am currently using the following:
=DSum("[Hours]","TestTable1","[RequestDate]='Forms! Form1!'Me.Date'")
I have tried this without the Forms designation; without the "Me" designation; Etc.
Some attempts return the Error or Name error while other efforts return a blank textbox...
View 4 Replies
View Related
Nov 2, 2014
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
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working
Sdate is date and it is on main form
View 6 Replies
View Related
Jul 30, 2007
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]")
View 3 Replies
View Related
Mar 29, 2008
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
View 7 Replies
View Related
May 17, 2005
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!
View 5 Replies
View Related
Jul 5, 2005
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 Related
Oct 2, 2006
Hi!
Can someone tell me why the following querie doesn't work?
txtcharges = DSum("[Charges]", "tblcharges", "[DateCharges] >= '" & startdate & "' AND [dateCharges] <= '" & enddate & "'") '
Thanks
JonyBravo
View 3 Replies
View Related
Apr 23, 2007
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
View 5 Replies
View Related
Jul 17, 2007
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
View 9 Replies
View Related
Jan 7, 2008
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.
View 4 Replies
View Related
Apr 24, 2006
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
View 5 Replies
View Related
Mar 2, 2006
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
View 1 Replies
View Related
Dec 29, 2005
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.
View 1 Replies
View Related
Aug 14, 2006
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 ?
View 1 Replies
View Related
Feb 21, 2008
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.
View 8 Replies
View Related
Oct 13, 2005
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
View 12 Replies
View Related
May 22, 2006
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
View 1 Replies
View Related
Jun 11, 2006
I have a Qry field:
TotalUsage: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblDetail]",),4)*100))
the problem is that the DSUM needs to sum up all the like records using the same SubID. I need some extra code in the divisor part. EG
subID uomUsage TotalUsage
33 10 100
33 40 100
33 50 100
34 43 100
34 57 100
the dsum needs to use SubID in order to sum correcly EG on SubID 33 as 100 uomUsage.
SubID is a Number field, Long integer.
I am presently getting wrong info. the formula above Dsum is summing as 200 since i eroneously don't have Subid in the Qry calc.
How can i fix? Thanks
Penwood
View 1 Replies
View Related
Sep 5, 2006
I have been trying to get this all night. This was my last attempt. I know it is all wrong and messed up but after 10 hrs I go for help. There is no date field to work with.
Expr2: DSum("[Amount]","[List]","[Item]=" & [Item] & "and [FinItem]>0")+0
But it ended up like this.
Item ------Location-------Amount--Expr2----Expr3
210090------030005 -------267470--error ----859103.74417
220170------ GYM -------409000--error ----859103.74417
220170------ 060010-------83297 ---error ----859103.74417
220170------ 050010-------17762 --error ----859103.74417
3429B -------060010-------0.74417--error ----859103.74417
3429B -------060010-------3 ----error ----859103.74417
7342900-----060010--------5369 ---error ----859103.74417
7342900-----060050--------76202 --error ----859103.74417
I need it to look like this.Where it will do a running sum based on the "Item" Groups.
Item---------Location------Amount----Expr2------Expr3
210090------ 030005-------267470---267470---859103.74417
220170------ GYM --------409000---409000---859103.74417
220170------060010-------83297----492297---859103.74417
220170------050010-------17762----510059---859103.74417
3429B-------060010------- 0.74417---0.74417---859103.74417
3429B-------060010------- 3-------3.74417---859103.74417
7342900-----060010-------5369----5369-----859103.74417
7342900-----060050-------76202---81571----859103.74417
Please Help.
View 3 Replies
View Related
