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.
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.
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())))'")
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
Hi all,
I need to be able to add x months to a given date which I'm using in a criteria expression. I've figured out that I can just add y number of days, but the answers aren't quite accurate across different ranges of months.
I'm after something like
<[BeginDate] And >([BeginDate]+[3months])
but I haven't turned up anything useful in an hour of googling - finding it difficult to define what I want in search engine terms.
Cheers,
Alex
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 RelatedI'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"?
I have a database with two tables: Customers and Transactions. The Customers table has fields for all personal information related to each customer including a Principal_Balance field which is the dollar amount the customer owed us to start with. The Transactions table has three fields: our customer account number ACCTNBR, payment AMT_LAST_PAY, and adjustment ADJUSTMENT. Obviously, when the customer makes a payment, we enter it into the payment field. The adjustment field is for any dollar amount change that needs to be made to the account.
I have generated a report that includes all of the customer information and I am trying to add an unbound text box that will total all of the payments and adjustments from the Transactions table by each customer account number.
My expression looks like this:
=DSum("[AMT_LAST_PAY] + [ADJUSTMENT]","Transactions")
I think I have the correct syntax in order to total the payments and adjustments fields, and to specify that the fields are contained in the Transactions table. I need to figure out how to write this so the sum is only the total for each account number ACCTNBR which is my key field in the customers table with a relationship to the transactions table.
I hope that all made sense. If anyone can help, I would really appreciate it.
hanks,
Mark A.
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
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
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.
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.
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
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
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).
Before I generate a particular report, the user must input a start and end date. If the user mouses to the generate report button, the query fails to grab the second date that was input. If the user tabs off of it, then it works just fine.
I think I could solve it by requerying the form before I generate the report, but that feels like a bulky work around. Is there a better way?
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies View RelatedI'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 am trying to do DSUM as a control in the footer of a report which would sum up values in one field [Charge_Qty] only if certain criteria are met for another field [CPT_HCPCS_Code]. The problem is that I cannot use the proper syntax for the multiple criteria but am successful if I only have one value in the criteria. ok this works below but I need to use multiple values such as 93303 - 93321, 93662, 93350-93352
=DSum("[Charge_Qty]","DIVISIONQueryDATEYEARTEMPLATE","[DIVISIONQueryDATEYEARTEMPLATE]![CPT_HCPCS_Code] = '93303'")
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()))
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.
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 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 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" ?
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...
I am running a query which returns daily sales numbers a also calculates a percentage increase. The query works fine until I try entering criteria to give any percentages that are >150 or <50, when running this no results are returned. Any help on this would be greatly appreciated.
Thanks.