How Do I Count Date Fields?
Jul 25, 2006
Hi there. Im trying to work out how I can count the dates in my database per month. What ive done is created an unbound box with an sql statement
SELECT Count([tbl Main].[Date of Call]) AS [CountOfDate of Call]
FROM [tbl Main]
HAVING (((Count([tbl Main].[Date of Call])) Between #4/1/2006# And #4/6/2006#));
When I select the totals button to count the records 0 are pulled out.
Does anyone know if im doing something wrong here. As always im sure its quite simple..
Cheers
View Replies
ADVERTISEMENT
Feb 13, 2014
What I have: A table with multiple Date/Time columns. Here's an example:
What I would like: A month-by-month summary counting the number of entries in each of the three columns, like this:
I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).
Smaller Pieces: I am able to create such a summary for one column at a time:
Not what I'm looking for: I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:
I have attached the example file for your convenience: Database1.accdb
To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)
View 6 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Oct 24, 2013
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
View 4 Replies
View Related
Jun 5, 2006
I'm building a health data system that stores data from health tests as follows:
[test01], [test02], [test03],.........[test25], etc etc.
Each of these tests comprised of a "Pass"/"Fail" value.
Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].
So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?
ie count([test01] [test02] [test03] [etc] [etc], ="Pass")
As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.
Many thanks in advance for any tips.
View 3 Replies
View Related
Oct 12, 2005
Hey,
I'm currently working on a database for my school project.
Currently, I have a table:
Job:
Contractor_ID [Foreign Key]
...
Contractors:
Contractor_ID [Primary Key]
Contractor [Text]
Basically I want to create a query that shows a table like so:
Wages:
Contractor [Contractors.Contractor]
Jobs_done [counts the number of rows from Job that has the Contractor_ID equal to the Contractor_ID specific to Contractor (above)]
Eg.
Job:
Contractor_ID
1
2
3
2
1
4
Contractors:
Contractor_ID Contractor
1 Bob
2 Bill
3 Jim
4 Jill
Wages:
Contractor Jobs_done
Bob 2
Bill 2
Jim 1
Jill 1
View 1 Replies
View Related
Jun 26, 2006
hi there,
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Thanks
View 2 Replies
View Related
Jun 26, 2006
hi there,
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Thanks
View 3 Replies
View Related
Oct 14, 2007
Hey all,
I'm having a hard time getting the output below from a query and was wondering if anyone would be able to reproduce this. Basically I'm trying to retrieve products that have more than 5 sales on a specific date and the retailers they sold at. I have the following simplified table.
Table name: SALES
PRODUCT RETAILER SALESDATE
---------------------------------------
PRODUCT1 COMPANY1 10/10/07
PRODUCT1 COMPANY2 10/10/07
PRODUCT2 COMPANY1 10/10/07
I am trying to find which products had more than two sales on the same date, and retrieve the product name, the retailer and the sales
I.E THE ABOVE TABLE WOULD PRODUCE
PRODUCT SALESDATE RETAILER
--------------------------------------
PRODUCT1 10/10/07 COMPANY1
PRODUCT1 10/10/07 COMPANY2
I've been able to only get the first two fields successfully by using
SELECT PRODUCT, SALESDATE
FROM SALES
GROUP BY SALESDATE, PRODUCT
HAVING (Count(PRODUCT)>=2);
MY problem is getting the retailers to appear also. Any help would be appreciated.
View 3 Replies
View Related
Feb 1, 2006
How do you sort on a count field in a report
View 4 Replies
View Related
Jan 15, 2007
Morning All!
Boss-Man has a db (humongous) that (along with a zillion other things) tracks which specific tasks have been completed, who has done it and when did they do it.
All tasks will eventually be completed by all employees.
He's got a query that returns all of the dates on which each task has been completed, using each TASK name as a field.
The short version below shows the TASK1 was completed on 1/1/07, 2/7/06 and 2/17/07, TASK2 was completed on 01/05/07,by 2 people on 2/15/07 and again on 02/28/07 and so on.
TASK1***** TASK 2***** TASK 3 ***** TASK 4
1/1/2007 *** 1/5/2007*** 1/13/2007 *** 1/2/2007
2/7/2006 *** 2/15/2007 *** 1/7/2006 *** 1/15/2007
********** 2/15/2007
2/17/2007******
********** 2/28/2007 ********* 2/28/2006
What he would like to do is be able to show how many tasks were completed in any given week, such as:
WEEK ENDING # TASKS
01/06/07 3
01/13/07 1
02/17/07 2
Looks to me like you'd first have to identify the possible WeekEnding dates and then try to total across all the fields, but I've not got a clue how you'd do that.
Is there an easy way to "query the query", work with Week Ending on Saturday, and get the info he's looking for?
Thanks,
BeckieO
View 1 Replies
View Related
Aug 1, 2005
Hey Guys,
I’ve attached a zipped example of the problem I am having with customised navigation buttons.
In my main database the form “subfrmPersonsContact” works perfectly. I have exported it to a new database and now the customised record count fields do not work at all – they are in fact blank even though the navigation buttons do still work.
The code running the form and the record count fields is listed below – I have no idea why it’s not working.
Any help would be greatly appreciated.
Cheers,
Rusty
:D
Private Sub Form_Current()
On Error GoTo err_Form_Current
Dim rs As Recordset
Dim Count As Integer, Position As Integer
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
Count = rs.RecordCount
Me!txtRecCnt = "of " & Count
Position = Me.CurrentRecord
Me!txtRecPos = Position
If Position = 1 Then
Me!gotoPrevious.Enabled = False
Me!gotoFirst.Enabled = False
Else
Me!gotoPrevious.Enabled = True
Me!gotoFirst.Enabled = True
End If
If Position = Count Then
Me!gotoNext.Enabled = False
Me!gotoLast.Enabled = False
Me!txtRecCnt = "of " & Position
Else
Me!gotoNext.Enabled = True
Me!gotoLast.Enabled = True
Me!txtRecCnt = "of " & Count
End If
rs.Close
exit_Form_Current:
Exit Sub
err_Form_Current:
If Err.Number = 3021 Then
Resume Next
Else
Resume exit_Form_Current
End If
End Sub
View 1 Replies
View Related
Jul 21, 2006
Is it possible to have a count of all records matching a certain criteria displayed on the form?
I have to schedule customers for certain dates but never know how many have been scheduled by date while in the form. I am not sure how to reference the field from the table to be counted. I have several unbound boxes with:
=date()&" "&{XXXX}([SCHEDULE DATE]=date()
=date()+1&" "&{XXXX}([SCHEDULE DATE]=date()+1
=date()+2&" "&{XXXX}([SCHEDULE DATE]=date()+2
etc.
not sure what to use for {XXXX}
As always, any help is appreciated.....
Someday I will contribute some answers instead of questions.....
View 1 Replies
View Related
Jan 3, 2005
Good day:
Code:
=DCount("[DEPARTMENT]","[Query1]","[DEPARTMENT]='ENGINEERING'
I have a query with these fields: DIVISION, DEPARTMENT.
The DIVISION fields are populated with undergrad or grad. The DEPARTMENT fields are populated with ENGINEERING.
I wish to count the occurances of undergrad engineering and count the occurances of grad engineering and return the values in separate text boxes.
Any ideas on how to count occurances of TWO fields?
Thanking in advance.
Dee
View 2 Replies
View Related
Apr 13, 2013
If I have a Report, with three fields (all data-type Text) named 'Jan', 'Feb' and 'Mar', and I want to have a fourth field (Unbound) alongside them, providing a count of the number of fields out of these three fields that are populated.
View 2 Replies
View Related
Jul 8, 2005
I have a query that uses 2 Fields from a large table. Cashier and Date. The problem I am having is I would like the select a time frame, a Start and Stop date, and count how many times the cashier comes up for that day.
Sounds easy, I would think so but having a problem. The dates are automatically imported into the large table as format 01/01/2005 12:00pm (Can't do anything about this).
When writing the SQL and the Count function, it is using the time as well in its calculation, but I simply want it to count on the date only. So unless a cashier existed exactly in the table as 01/01/2005 12:01pm, 01/01/2005/12:01pm it only counts it as a total of 1, instead of twice for that day.
I simply want the SQL function to read the Date field as 01/01/2005, and drop off the Time. I can format it, but only changes it visually and the SQL statement still sees the time.
Any help would be appreciated.
I found this code on the boards here, almost what I want but does not group each Cashier by day and total, it does the total of all cashiers for that day. I need the Cashiers sorted for each day, and totalled.
SELECT Format([Date1],"dd mmm yyyy") AS Day1, Count([ Cashier1].[Cashier1]) AS CountofCashier
FROM CashierQ1
GROUP BY Format([Date1],"dd mmm yyyy"), Format([Date1],"dd mmm yyy")
ORDER BY Format([Date1],"dd mmm yyyy");
View 6 Replies
View Related
Feb 17, 2005
Hello everyone, I have a pretty simple form regarding posting message on a certain forum....
The form contains nick mane, date, hour and few more stuff regarding the post.
How do I create a query that will count all of the posts that I typed in for a specific date?
I tried count but it won't work.....
View 3 Replies
View Related
Jun 27, 2013
I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :
Code:
SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code
FROM Contacts
WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));
Is there anyway to count how many fields are missing for each record ?
View 2 Replies
View Related
Jul 20, 2013
i have a access table (AUTH) with following field
Company Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
i used the following query to calculate the Held from a table named pers.
SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth
then i got the result as under
Company Auth Held
Tata 12 02
Dell 11 08
HP 21 20
Opera 11 12
Bangour 10 12
now i want the difference between Auth and Held as Sur/Defi
how can be it done.
View 2 Replies
View Related
Nov 20, 2013
I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).
I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.
View 3 Replies
View Related
Feb 11, 2014
I have a terrible skill in this but I am doing my best to find ways. But I am running out of time in the development stage. I have the following form and table:
Tables:
Eqpt
EqptHistory
Forms:
frmEqpt
frmHistory
frmEqpt is the Mother form and frmHistory is the subform connected thru eqptID. Now what I want to do is count the records in the frmHistory and Locked the fields for editing. If it is new records, the fields will automatically unlock.
How do I do that because I can't find a way to count the fields with records and lock them.
View 9 Replies
View Related
Apr 29, 2014
a table which has several fields (ProjectID, Name, PM, Category, Date, Tester).
I am trying to find a way to count how many of the fields have been filled out and how many are null for a particular Project ID.
For example, ProjectID=27 has a Name and a PM but no more data so when I load the form I want the db to tell me 3 of the fields have been filled out for this project. This way I can calculate how many are empty (3 in this case)
How would I go about doing this
View 6 Replies
View Related
Jul 29, 2005
I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!
View 3 Replies
View Related
Nov 1, 2005
I have a table that includes client account information. I want to create a query that will select all account numbers where the last updated date is less than or equal to a number specified by the user.
Table (ClientRequests)
AccountNumber LastUpdated
76869 5/10/2005 9:49:38 AM
22151 10/29/2005 10:35:23 AM
so for example: I would like to display all accounts where the differrence between todays date and the LastUpdated date is <=10. Where 10 would be a user defined number.
Hope this makes sence.
thanks in advance
View 2 Replies
View Related
May 8, 2006
I am trying to run a query on a table [tblMain]. I have an input form where the user enters a date range. I would like to count the Month to Date Sales. The month would be that of the end date (forms!frmflash!enddate). How could I count the number of sales between the first of the month and the end date? Each record has a salesdate1, salesdate2, salesdate3, salesdate4, salesdate5, and salesdate6. I need to look in each of these fields and count the sale if it is between the 1st of the month and the enddate.
View 3 Replies
View Related
May 29, 2014
I have a form who's control source is a query which displays dates that personnel and equipment are mobilized.For invoicing on a weekly basis i need to display the number of days within the week that these are on hire.The mobilization and demobilization dates may be well outwith the weeks dates or may be within the week though.
For example MobDate 20/03/2014, DeMobDate 13/06/2014 but the week is this week.or Mobdate 27/05/2014, DeMobDate Today 30/05/2014
I need to display the actual dates if within the current week or display the first day of the current week and the last day of the current week if the mobilization and demobilization dates are outwith the current weeks dates?I have created two unbound textboxes MobTextBox and DeMobTextBox and am trying to use the code below to insert what i described above.Once i have the dates inserted i have a third textbox with the ControlSource set to =DateDiff("d",[MobTextBox],[DeMobTextBox])
Dim MobDate As Date
Dim DeMobDate As Date
Me.MobDate.SetFocus
If Me.MobDate.Text > (Date - 7) Then
Me.MobTextBox.Text = Me.MobDate.Text
[code]...
View 5 Replies
View Related
