Forms :: DSum With Multiple Criteria
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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
Jan 10, 2014
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'")
View 2 Replies
View Related
Apr 11, 2014
I have a sub form that shows me a companies history. This subform is used on 2 different Parent forms. The record source for the sub form uses a criteria that looks at a companyID field on the parent to determine which records to return. My question is how do I have the query criteria depend on which form is currently opened? The criteria would look something like this:
[Forms]![frmCompanyHistory]![txtCompanyID] OR [Forms]![frmCompany_Project_Details]![txtCompanyID]
If I open the form with this criteria, I will be asked for a value in txtcompanyID for whatever parent form is not open. How do I properly do this without making multiple queries that are almost identical?
View 5 Replies
View Related
Feb 27, 2008
I'm sure this one has been asked time and time again!
I currently have a set of staff questionairres each on a different table.
Currently when I want to export all the scores for one employee to excel (eventually) I have a macro which runs 7 queries, but each asks for the same criteria (Date To & From) and (Staff Name) in turn - very time consuming!
How do I create a query or macro which will ask once for a date range and an employee name and apply to all of my queries?
I am a novice on access and am trying to do this without SQL
Thanks for your help
View 1 Replies
View Related
Oct 20, 2014
I have a form where users can enter the beginning and ending dates into text boxes. They could also do it for the number of the week (1 to 52) or a single text box for the quarter.
I set up VBA code where, if you click on a button, it will clear out the data from the other two options before a query is run based on the parameters in those text boxes. Here's a sample piece of code.
Code:
Private Sub btn_UseDates_Click()
Dim lngGreen As Long, lngWhite As Long
lngGreen = RGB(0, 255, 0)
lngWhite = RGB(255, 255, 255)
Me!txtDateFrom.BackColor = lngGreen
Me!txtDateTo.BackColor = lngGreen
Me!txtWeekFrom.BackColor = lngWhite
Me!txtWeekTo.BackColor = lngWhite
Me!txtQuarter.BackColor = lngWhite
Me!txtWeekFrom = ""
Me!txtWeekTo = ""
Me!txtQuarter = ""
End Sub
I've tried setting those txt boxes to Null and I've tried both "" and " ", but to no avail. The query will work if the three types of ranges (date, week, or quarter) are on OR lines, but not on the same line.
View 5 Replies
View Related
May 18, 2013
I have a form bound to a table and I need to display and edit records. I want to narrow down the records by applying two filters; one combobox to select the ClientID and another to select the date. What I have so far is this:
Private Sub CboClientID_AfterUpdate()
Me.Detail.Visible = True
CboDate.RowSource = "Select AppointmentDate " & _
"FROM tblSample " & _
"WHERE ClientID = '" & CboClientID & "' " & _
"ORDER BY AppointmentDate"
End Sub
then in the cboDate afterupdate event I have:
Private Sub CboDate_AfterUpdate()
Me.Filter = "ClientID = '" & Me.CboClientID & "' AND [AppointmentDate] = #" & Me.CboDate & "#"
Me.FilterOn = True
End Sub
This appears to work with some records and not with others and I don't know why?
View 6 Replies
View Related
Apr 19, 2013
I have a form lets call it 'Customer' and I have a table called orders how can I write 'Excel like' Countifs and sumifs formulas? I have been trying the IIf functions I believe correctly but am not getting the results expected or rather nothing at all!
What I am wanting to do is for example look at the table 'orders' and count all 'orders' which are for the 'Customer' in the active form this field is imaginatively called 'Customer Number' and 'orders' that are for simplicity 'rocks'
Basically if the client in the active form exists in the orders field count how many times they have placed an order for rocks.
View 1 Replies
View Related
Feb 4, 2015
I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is:
[Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field.
The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].
My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?
View 3 Replies
View Related
Jan 31, 2015
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from
[L3-4-5] is my main table
[CDATE] is a form control where the user enters a date
[Quarter] is a number field and want it to equal 1
[Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
Me.DAYS_TRAINED.Value = DLookup("Days", "TRAINED LH A-PILLAR", "ID=" & FTM_NAME)
So I think I don't understand the multiple criteria part.
View 3 Replies
View Related
Oct 8, 2013
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered.
- for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june".
- This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
View 4 Replies
View Related
Nov 15, 2013
I have a form with combo box named "TEXT17" i have an another text box named(Basic Pay). How can I use dlookup creteria with two condition
EMPID= text17 value and
month in lbl9 value="mar"
I also paste my code here but show error
Me.Text43 = DLookup("Basicpay", "Salaryall", "[EMPID]='" & Me.Text17 & "'" And "[Month]='" & "Mar")
View 6 Replies
View Related
Jun 20, 2014
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
View 4 Replies
View Related
May 30, 2015
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code
Code:
Private Sub SearchBtn_Click()
PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
End Sub
This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code
Code:
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &
I get an Compile error saying "expected expression".
View 3 Replies
View Related
May 15, 2013
I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:
Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.
Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.
When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.
Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this
I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:
Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.
View 1 Replies
View Related
Aug 14, 2015
I want to result as dlookup function in access 2007, when I use dlookup like this
=DLookUp("GPFDed","[SalaryAll]","[EMPID]='" & ![Cmb_EMPID] & "' And [SalMonth]='" & "Jan" & "' And [Salyear]='" & [me]![Label17].[Caption] & "'")
it gives #Name?
What is the problem and how can i change the label caption to Combo box column 2 value...
View 1 Replies
View Related
Jun 14, 2015
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String
[Code].....
View 3 Replies
View Related
