Forms :: Counting In Forms When Meeting Criteria
Feb 23, 2014
I have created a form that list the hours that people do for different types of activities. What I want to do is to sum the hours of each activity. However, I have some activities that I want to group.
For example, I have Training, Ride Outs and Mentor as types of activities that I want to group and then there are all other types that I want to group as Other.
I would just like to add a formula into the command box of properties.
View Replies
ADVERTISEMENT
Jan 13, 2014
Using MS Access 2000
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
=DCount("MemberID","tblHHMembers","CustID = [CustID]")+1
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
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
Nov 16, 2014
I have 7 combos, which for the most part set the value in another field - however, there are two values in the combo that do not have a value attached - one is AH, the other is SK. I have two textboxes (one for AH, one for SK) that I wish to have a count (preferably running) of how many times each of these values appears in the seven combo boxes.
View 5 Replies
View Related
Aug 26, 2014
I have a database where I have 2 fields that are "Person Start" and "Date Start"
I would like to show in a text field how many cases a person started on a certain date or better on today's date.
I tried the below, but did not got it to work:
Code:
=Count([PersonStart]="Peter"),[DateStart]=Today())
what I'm doing wrong?
View 12 Replies
View Related
Aug 27, 2013
I have a form that has three cascading combo boxs, plus a subform. The third combo has a list of questions, and you put your answer in the txt box in the subform.
What has to happen is that the combo box has a number of questions and each has to be answered, is there a way to know if all these questions have been answered by knowing what listindex or listcount ect that has be used and to have something set to advise the user that there are more questions and what ones are to be answered.
View 3 Replies
View Related
May 24, 2013
I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.
View 1 Replies
View Related
Feb 7, 2014
I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as
=Count(IIf([Gender]="Male",0))
But I get #error on everything.
View 12 Replies
View Related
Feb 18, 2014
I have set up a database to record several different sets of records for a volunteer group that I run. The types of records that I am keeping are hours on duty, uniform issued and attendance at training. The attendance form is a subform which contains a list of dates and the two fields of checkboxes showing if they attended or sent their aplogises.
What I am trying to do is to count the number of times that members attended training. I have set up a 'unbounded' text box in the footer and would just like to add a formula in to the command box of the box properties. Anything more i.e. coding then it goes over my head very quickly.
View 3 Replies
View Related
Oct 2, 2013
I would like to count a field if another is empty. I have 2 fields "registered" and "Started" and "closed"I would like to count all the fields "registered" if the field "started" is empty. I also want to do it with "started" if "closed" is empty.
View 2 Replies
View Related
Sep 15, 2014
I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).
View 14 Replies
View Related
Aug 27, 2014
Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....
View 1 Replies
View Related
Oct 24, 2014
I am trying to use =DCount() on a report and its half way working, it does count records acording to the criteria I asign the problem is its using the whole universe of records in the table so I have a report displaying 10 records of which 953 are Red and 752 are Blue...
View 14 Replies
View Related
Jun 29, 2007
My company has a meeting every tuesday where we discuss the status of all our construction jobs. I am trying to set up a database which will allow me to build a report which shows the job number and job status of all our jobs on that particular meeting date. I would like to be able to view the entire job list and pull up the job list by date. The fields I'm trying to store are job # and job status. But I'd like to be able to go back 3 weeks and pull all the job status notes for the entire job list. I can do this by entering a job status field in the job list table but I'll end up having alot of fields in the table which I want to avoid. Is there another way I can enter this info into the database?
Thanks,
Paul
View 2 Replies
View Related
Jan 18, 2005
I have a table where for every customer I have they have two lines of data. Both lines of data contain their customer number and then one line has the qty of tickets they have ordered and the second line of data has their name. How am I able to combine this data into one line? I would really appreciate anyone's help. I need this for the morning and I am looking at combining it manually so any help would be appreciated. Thank you.
View 2 Replies
View Related
Sep 22, 2005
I am trying to create a query to be used by a report where the criteria are based on certain controls in a form.
EG ‘forms![FormName]![Control]’
As the fields are date fields, I am using 2 controls in the form and the ‘Between’ operator to choose a ‘From’ and ‘To’ date to search.
EG ‘between forms![FormName]![Control1]) and forms![FormName]![Control2]’
Not all of the records in the various date fields have dates in them, some are just null.
I wanted the query to look at the form and see that if the date controls in the form were blank (or null) then show all records for that field in the query. If the controls in the form have dates in them, then just filter the records in the query that meet that critera. (i.e. the dates in the control.)
The problem is I can’t find a way to do it!
I tried this in the query criteria for a particular date field:
Between [Forms]![Frm_Returns]![ReturnsFrom] And Forms]![Frm_Returns]![ReturnsTo] Or Not Like "*" Or Not Like IIf(IsNull([Forms]![Frm_Returns]![ReturnsFrom]),"!","*")
That seems to work for just one of the date fields, but I have 8 date fields in the query and 8 controls in the form that I need to enter criteria in a similar way. When I try doing this on more then 1 field, it just doesn’t work!
Can anyone help please?
View 2 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
Feb 13, 2014
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code:
Shell ("Outlook.exe")
Dim outMail As Object
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Recipients.Add (Me.txtsupervisor)
[Code] .....
View 3 Replies
View Related
Jan 24, 2012
I would like to design and build a database that can match one to one meetings between companies with specific time slots.
For example, we have two sets of people: buyers and sellers. A buyer could specify which sellers they would be interested in meeting. The database would then work out which buyers could meet with which sellers, and when.
View 5 Replies
View Related
Feb 23, 2005
Hi,
I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].
I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?
What I have is:
IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)
Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?
Any suggestions? Thanks!
View 5 Replies
View Related
Feb 12, 2006
Hello everyone,
I have a problem with opening forms.I have situation with 8 forms (small ones!) which must be open based on values in ComboBox1 and ComboBox2.
Can I do it with Select case (DoubleClick event), and how?
THANK YOU IN ADVANCE,
View 4 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
Jul 5, 2013
what's the problem of the code below?
I want to create a filter with more than 2 criteria
Me.FilterOn = True
Me.Filter = "[YEARS] = " & Ycode & "[years] ="&Ycode& "[months]="&Mcode& "AND[POScode] = " & Pcode
View 9 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
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
