Queries :: DSum Does Not Recognize Dates In Criteria Box
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).
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value) SELECT s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name FROM supply_points s , codes c WHERE s.code = c.code and s.id is not null and s.code = 1075 and s.month >=[forms]![main]![gppstart ] group by s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name pivot s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
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:
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.
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
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.
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
I have a table which list a load of items, one field is date and one field is time.I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.This then runs a query for a report to be produced. The problem I having is getting the query to runs both criteria it returns nothing.here is the Where part of the current SQL.
WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate] And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea] And [Forms]![FRM_SearchMulti]![txtreptimeb]))ORDER BY tblIncident.IncDate, tblIncident.IncTime;
I am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code: Dim ResultCount As Long Dim DateSearch As Date Dim MyDate As String Dim MyDateAdd As Date Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?
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.
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
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.
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.
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.
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
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind. The save code is:
Dim dbs As dao.Database Dim rst As dao.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset) rst.FindFirst "ID = " & Forms!frmInvoice!txtID With rst .Edit !Total = Forms!frmInvoice!txtTotal .Update End With Exit Sub
txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.
I'm attempting to do a DSUM in a query against a linked table that has a field name of "VOID". The field name shows up as a yes/no when looking at the structure and when looking at the data, it is either 0 or -1. I've tried just about everything I can think of but this is the structure I have:
Tons: DSum("Net","dbo_tblSALESTKT","JOB='" & Left([JobNumber],6) & "' AND Ticket_Date=#" & [forms]![frmForemanInfo]![CurrentDate] & "# AND [dbo_tblSALESTKT]![VOID]=0")
If I remove the VOID part, it will sum correctly but when adding in the VOID=0 or false or No or anything, the query doesn't show me a sum anymore. what I'm doing wrong.
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.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)
I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:
Reason Count Per Expr1 A 35 47.9 Random Numbers B 11 15.1 " C 10 137 " D 9 12.3 " E 7 9.6 " F 1 1.4 "
The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
