Access :: Problem With DSum Expression

Reports :: Date Criteria DSUM Expression

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

Writing An Expression In The Expression Builder

Jun 8, 2005

Good day all

Heres what im trying to do:

in one textfield i have =Count(*)

this returns all the records in my db.

now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31

please help

lee

View 1 Replies View Related

Should I Use DSUM?

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

Using Dsum()???

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

DSUM() I Think!

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

DSum Help

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

Dsum

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

Dsum... I Think!

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

Help With DSum

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

DSum?

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

Dsum Overflow

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

Dsum Problem

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

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 7 Replies View Related

DSUM Problem

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

DSum With JOIN?

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

Help With Dsum/Query In Form

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

Sync A DSUM To A Record

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

DSUM And Date Problem

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

Update Query And DSum

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

Dsum Need 2 Fields In Calc

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

DSUM / Running Sum In Query

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

DSum Not Working Properly

Dec 8, 2006

I can't figure out what I have wrong in my code. I've searched all over the forums and the help files in Access. I'm sure it's something simple. Could someone please take a look?

=DSum([DefectQty],"d_sub1_DefectReport","CodeID= "" & [tblDefectCodes].[CodeID] & "" AND ""[InspectDate] >= #" & [Forms]![frmDefectDetailReport]![txtStartDate] & "#" And "[InspectDate] <= #" & [Forms]![frmDefectDetailReport]![txtEndDate] & "#")

I'm trying to get a sum of all defect quantities between a date range from a user form. Rather than summing all the defect quantities for a defect code, it's summing up the total quantites for each quantity. I'm sure that doesn't make sense so here's an example:

InspectDate CodeID DefectQty DSum Should Be
6/1/06 100 1 3 5
6/2/06 100 2 4 5
6/3/06 105 1 3 1
6/4/06 103 1 3 1
6/5/06 100 2 4 5

The domain is a query because the dates come from a different table than the defect quantities, so the query is pulling all the relevant data to be summed.

View 6 Replies View Related

Dsum Function In A Query

Mar 19, 2007

hello
i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.

example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.

I can not get the dsum function to work???
any
ideas????

i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.

View 1 Replies View Related

Aggregate Function DSUM

Feb 17, 2008

Hi All,

I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.

To calculate the TDAYS i need to SUM the DAYS column for every employee.

When an employee again log in the application he should be aware of the total number of leaves which he has already availed.

Please help me for the same as I am unable to do so.

Kindly revert back for any queries or any further information.

With Warm Regards,
Sanjay

View 3 Replies View Related

DSum, Specific Links Or Anything Else?

Feb 22, 2008

Dear all, imagine a table named "Database" with fields

Par1, Par2, ... ParN, Amount,

where most of the parameters in Par1,...ParN are in text format.
The structure of another table, "Criteria", is exactly like "Database" less "Amount" field.
Suppose we want to summarise "Amounts" from "Database" according to the sets of "Criteria", for each of its records. A set does not always include all of the fields, e.g. an empty field (or sort of wildcard) may mean that any value fits.
It seems like classical application of DSum function, but I've already spent a lot of time in desperate attempts to solve this problem and will highly appreciate your suggestions.

View 1 Replies View Related