Queries :: DSum Using Date Criteria
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 Replies
ADVERTISEMENT
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
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
May 15, 2014
I can't seem to get my head around an issue i'm having with a DSum, the statement is:
Code:
=DSum("[Amount Used]","[tbl_Rwk_Used]","[Cage Number]='" & [IBC_Number] & "'" And "[ID]=" & [ID])
I've tested both statements separately and they work ok, but as soon as I add the "AND" operator, the whole field is summed and the criteria thrown out of the window apparently.
[ID] Is the same in both tables and relates to a main record
[Cage Number] and [IBC_Number] are related in both tables, but are and are strings
View 2 Replies
View Related
Jul 9, 2013
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
View 12 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
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
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
Apr 21, 2006
Hi!
I have two related tables, one containing human resource information, the other containing a weekly record of hours worked by employees.
Here's the dilemma:
I need to keep a running total of hours available based on hours worked.
Employees begin each 7 day work week with 70 hours of time available. Any hours worked in that week are subtracted from the available hours for that employee. If there are two days in a row not worked during any given week, the 70 hours needs to automatically reset.
These hours are tracked in the Hours Available field (a calculated control which needs the Dsum expression) which should examine the Hours Worked field. Presently, the Hours Worked control gives a total of all hours worked for the employee shown on screen, but I need to examine the current work week to see whether or not an employee has worked more than 70 hours (it's a legal requirement.) If during any 7 day period counting backwards from the present date the employee had two consecutive days off, the employee becomes eligible to work 70 hours and the countdown starts again. In short, the total hours worked should be subtracted from 70 until 0 is achieved in any given week, unless the employee has had two days off (Hours Worked sum for two consecutive days =0.) I have attached a paired down version of the database for review.
Any ideas?
Tom
View 1 Replies
View Related
Sep 30, 2014
I'm trying to filter from dsum criteria the two values, here is my code :
Code: =DSum("Amount","qry_sumawpo","[Status] = 'FBLNG'" OR "[Status] = 'BLLD'")
How to correct this code. I need 2 different values in 1 criteria.
View 2 Replies
View Related
Apr 30, 2014
I have a dsum statement with multiple criteria that I cant get to work. Basically it returns no records, when it should return records that have a CategoryID = 1 and a State/Province = 14.
Code:
TotalSMECount = Nz(DSum("WorkingDays", "qryTotalUsageForDashboardNew", "CategoryID = 1" And "State/Province = " & Me.cboProvinceFilter.Column(2) & "")) + Nz(DSum("UsageDays", "qryEquipmentDaysRapidProtoCumulative", "CategoryID = 1"))
I am guessing that I just have the criteria portion written incorrectly Is there anything obviously wrong? For the record, when I debug.print Me.cbo Province Filter.Column(2) it returns the value '14', which is correct for my test data.
View 3 Replies
View Related
Jan 2, 2015
I'm using a very simple unbound textbox on a very simple form, with the following Expression to find me data in a query and sum the last 30 days, based on a combo-box on my form...
Only thing is, it's showing me only the data from the last 30 days...regardless of the value in the combo.
=DSum("Credit","qryIncVsExp","TransDate>=#" & Date()-30 & "#" And "AccountID_FK="""&[cboaccount].[column](1)&"")
I'm sure it's something to do with the number of "'s I have, but I'm probably more than likely ever so wrong.
View 1 Replies
View Related
Mar 23, 2013
I am trying to get deposit amount for the current month but results are in accurate i am using this
DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))
View 3 Replies
View Related
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
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
Dec 10, 2014
I have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.
Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?
View 3 Replies
View Related
Apr 1, 2014
I am trying to figure out how to build my query that returns equipment reservations where "date out" OR "date In" are between 2 dates that are specified on my form using [Forms]![Myformname]![StartDate] and [Forms]![Myformname]![EndDate].
The purpose of the query is to find the amount of companies that have had a piece of equipment reserved between 2 dates, which could be a calendar year, or a fiscal year for example.
This is my current SQL statement that returns records where only DateIn falls between the criteria. My objective is to also return records where DateOut falls between 2 dates.
Code:
SELECT tblReservations.ReservationNumber, tblCompanies.CompanyName,
tblLookup_Contact_types.[Contact Type], tblReservation_details.Dateout, tblReservation_details.DateIn
FROM tblLookup_Reservation_Status INNER JOIN (tblCompanies INNER JOIN ((tblLookup_Contact_types INNER JOIN tblReservations ON tblLookup_Contact_types.[Contact Code] = tblReservations.CategoryID)
[Code] .....
View 4 Replies
View Related
Jun 24, 2014
I have a report database that provides my company with clients that took our training modules and notify us of which clients completed our trainings.The clients can complete training in 3 States and "Passed" means they are good to go.
I download an excel report daily and import it to Access on a daily basis. Problem is the Report is over 8,000 rows long and basically I just need the clients that completed training within past 48 hours. The excel report provides a date of completion.
code that only pulls those clients that "Passed" within the last 48 Hours. Here is my SQL Statement I use on the RecordSource.
SELECT report.SPS, report.FirstName, report.region, report.id, report.AZ_Cert, report.AZStatus, report.CA_CERT, report.CAStatus, report.OR_CERT, report.ORStatus, report.Completed FROM report WHERE (((report.Completed)=False));
The completed checkbox removes the record from the cert queue. How can I do this more efficiently? I think I have it right.
Private Sub Completed_Click()
Const cstrPrompt As String = _
"Are you sure you want to complete this record? Yes/No"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
If Me.Dirty Then
Me.Dirty = False ' save the record
Forms!frmRecertView.subfrmRecert.Requery
End If
End If
End Sub
View 6 Replies
View Related
Jan 26, 2014
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
DLookUp("[Number Value]","Number Values","[Number ID]=" & [Number ID Index] & "And [Number Date]= [Initial Date]")
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
Why my Dlookup is returning a blank?
View 3 Replies
View Related
Sep 11, 2014
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
View 2 Replies
View Related
Feb 5, 2014
How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.
For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.
But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.
Is this even possible?
View 4 Replies
View Related
Apr 4, 2013
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies
View Related
Sep 23, 2014
Below is a simplified example of what I'm trying to achieve with a parameter query.
The source table for the query contains two fields:
Reading_Date (short date) and Use_value (integer)
The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:
SELECT Sum(Table1.Use_Value) AS SumOfUse_Value
FROM Table1
WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));
This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.
View 4 Replies
View Related
Jul 9, 2013
I am working with Access 2007. I have a little problem building the iif statement within a query criteria in [Actual Date] which is the following:
iif([Actual Date] Is Not Null And [Document]=0;>#01.01.2012# Or Is Null;999)
When I enter this I get no records out of about 8000. The table fields behind the query are in the right format (date/time and number).
View 1 Replies
View Related
Feb 8, 2015
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
View 11 Replies
View Related
Jul 31, 2013
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
View 3 Replies
View Related
