Hi there,
I recently started as a reporting analyst for a large firm. I was asked to work on a project where I import data from Outlook into Access and get a count of emails sent to and from each individual employee. I created a combo box with the control source set as:
=DCount("[To]","Inbox","[To]= 'Firm ABC'" & "Datepart('d',[Received])=Datepart('d',Today())")
[To] and [Received] are columns in a table named "Inbox". [Received] is in a date format. Essentially what I am trying to do in this function is to count the number of emails from 'Firm ABC' on the current date when you open the report. I have tried almost everything (ex. "& [Received]=Today()"). Any suggestions or help would be greatly appreciated!
Thanks
Hi Eric.
Well we've done the difficult part - extracting the information from you. The rest is easy.
Assuming you have a link to your oulook folder, I suggest you create 2 tables, and append the data to them so that there is no possibility of corrupting Outlook.
The TblContacts will hold only 1 copy of the Employee Name.
The TblMessages will contain a unique combination of Employee, Subject & Date
Set up the relationship between the 2 ContactIDs
Then build a continuous form like this:
In the examples below, my Outlook Folder is called Mike.
This is the append query to add the contacts. -
INSERT INTO TbContacts ( ContactName )
-
SELECT DISTINCT Mike.From
-
FROM Mike;
-
And this the query to append the messages -
INSERT INTO TbdMessages ( ContacID, Subject, DateReceived )
-
SELECT TbContacts.ContactID, Mike.Subject, Mike.Received
-
FROM Mike INNER JOIN TbContacts ON Mike.From = TbContacts.ContactName;
-
Below is all the code in the form which allows you to filter on a name or date range of combination of name & date range: -
Option Compare Database
-
Option Explicit
-
-
Dim Fltr As String
-
-
Private Sub ApplyFilt_Click()
-
-
Dim FDate As Date
-
Dim TDate As Date
-
-
If IsNull(FromDate) And IsNull(ToDate) Then
-
If Nz(CboContact) > 0 Then
-
Fltr = "ContactID = " & CboContact
-
End If
-
GoTo FilterIt
-
End If
-
-
If IsNull(CboContact) Then
-
If Not IsNull(FromDate) Then
-
FDate = CDate(Format(FromDate, "mm/dd/yy"))
-
If IsNull(ToDate) Then
-
TDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
Else
-
TDate = DateAdd("d", 1, ToDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
End If
-
End If
-
GoTo FilterIt
-
Else
-
If Not IsNull(FromDate) Then
-
FDate = CDate(Format(FromDate, "mm/dd/yy"))
-
If IsNull(ToDate) Then
-
TDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
Else
-
TDate = DateAdd("d", 1, ToDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
End If
-
End If
-
GoTo FilterIt
-
End If
-
-
-
FilterIt:
-
Me.Filter = Fltr
-
Me.FilterOn = True
-
-
RecCount = Me.RecordsetClone.RecordCount
-
-
End Sub
-
-
Private Sub Form_Load()
-
-
RecCount = Me.RecordsetClone.RecordCount
-
-
End Sub
-
-
Private Sub FromDate_AfterUpdate()
-
-
If IsNull(ToDate) Then
-
ToDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
ToDate = DateAdd("s", -1, ToDate) ' Take off 1 second to return toi previos day
-
End If
-
-
End Sub
-
-
Phil
10 3542
Try changing "Today" (AFIK This is not a word recognised by Access) to Date()
Why is this a Combo Box? I think it should be a Text Box
A further problem is that it is "hard wired" in that you can only let information from Company "Firm ABC", not from Firm XYZ
I suspect you should be looking at 2 input boxes (in the case of the firm, it might be Combo box), 1 to select the date and the other to select the firm whose statistics you need.
Phil
Thank you for replying Phil! I changed it to a text box and changed the formula to Date(), but it is returning an #Error value. I am now going to try the 2 box method. I am going to use a query to do "between [start date] and [end date]" as criteria. Any idea on how to create a function that will use a lookup for the combo box of a specific firm for the date range and count how many calls?
Thanks again,
Eric
Hi Eric,
We have to start gently.
Do you have a table containing at least the Firm's name and an AutoNumber filed like FirmID and the Email date?
If not, what is your table like?
Phil
The table's headings are as follows:
ID, From, Sender Name, To, Received (Date/Time), Modified (Date/Time)
Ideally, my boss wants a report where we can select the date range in the report and use a list/combo box to either select all employees or a specific employee. Then, he wants a text box that counts the # of emails received between a specific date and for that employee.
Ex. John Smith received 20 emails for the week 1/1/2018-1/5/2018
Much appreciated,
Eric
That looks quite hopeful, Eric
Can you clarify what data is in "From" and what is in "Sender Name". I presume one is the Email Address.
Where does the "Employee" fit into the picture?
Phil
Well, your optimism gives me hope.
We can avoid "From" because it is so similar to "Sender Name", but "Sender Name" is just a list of employee names, which is what he wants in the drop-down. "Sender name" seems to be a mix of both names and email addresses. Sorry I didn't specify that earlier.
Eric
Sorry, More questions.
Are you only interested in Emails from your Employees for this exercise, if so, how are you filtering them from your Outlook Folder?
Phil
No problem at all...ask away!
In Outlook, there is a folder that I was given administrative access to which contains the emails sent (up to 45 days of archives) from all of the employees in our team (about 150 people in our team). I created a table link between Access and Outlook, so everything in the table was imported by Access directly from the Outlook folder that I mentioned previously.
Eric
Hi Eric.
Well we've done the difficult part - extracting the information from you. The rest is easy.
Assuming you have a link to your oulook folder, I suggest you create 2 tables, and append the data to them so that there is no possibility of corrupting Outlook.
The TblContacts will hold only 1 copy of the Employee Name.
The TblMessages will contain a unique combination of Employee, Subject & Date
Set up the relationship between the 2 ContactIDs
Then build a continuous form like this:
In the examples below, my Outlook Folder is called Mike.
This is the append query to add the contacts. -
INSERT INTO TbContacts ( ContactName )
-
SELECT DISTINCT Mike.From
-
FROM Mike;
-
And this the query to append the messages -
INSERT INTO TbdMessages ( ContacID, Subject, DateReceived )
-
SELECT TbContacts.ContactID, Mike.Subject, Mike.Received
-
FROM Mike INNER JOIN TbContacts ON Mike.From = TbContacts.ContactName;
-
Below is all the code in the form which allows you to filter on a name or date range of combination of name & date range: -
Option Compare Database
-
Option Explicit
-
-
Dim Fltr As String
-
-
Private Sub ApplyFilt_Click()
-
-
Dim FDate As Date
-
Dim TDate As Date
-
-
If IsNull(FromDate) And IsNull(ToDate) Then
-
If Nz(CboContact) > 0 Then
-
Fltr = "ContactID = " & CboContact
-
End If
-
GoTo FilterIt
-
End If
-
-
If IsNull(CboContact) Then
-
If Not IsNull(FromDate) Then
-
FDate = CDate(Format(FromDate, "mm/dd/yy"))
-
If IsNull(ToDate) Then
-
TDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
Else
-
TDate = DateAdd("d", 1, ToDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
End If
-
End If
-
GoTo FilterIt
-
Else
-
If Not IsNull(FromDate) Then
-
FDate = CDate(Format(FromDate, "mm/dd/yy"))
-
If IsNull(ToDate) Then
-
TDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
Else
-
TDate = DateAdd("d", 1, ToDate) ' Add 1 day
-
TDate = CDate(Format(TDate, "mm/dd/yy"))
-
Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
-
End If
-
End If
-
GoTo FilterIt
-
End If
-
-
-
FilterIt:
-
Me.Filter = Fltr
-
Me.FilterOn = True
-
-
RecCount = Me.RecordsetClone.RecordCount
-
-
End Sub
-
-
Private Sub Form_Load()
-
-
RecCount = Me.RecordsetClone.RecordCount
-
-
End Sub
-
-
Private Sub FromDate_AfterUpdate()
-
-
If IsNull(ToDate) Then
-
ToDate = DateAdd("d", 1, FromDate) ' Add 1 day
-
ToDate = DateAdd("s", -1, ToDate) ' Take off 1 second to return toi previos day
-
End If
-
-
End Sub
-
-
Phil
Thank you so much for your help Phil and taking the time to do this.
-Eric
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dan Leeder |
last post by:
stroccur = DCount("", "empnotes", " = " & Chr(34) &
Me.Rpt_Card_Type & Chr(34) & " And = " & Me.SSN & " And #" &
& "# > " & DateSerial(Year(Me.datetime) - 1,
Month(Me.datetime),...
|
by: psuaudi |
last post by:
I am trying to use the following code, but I think there is an error in my syntax:
Count = DCount("", "Change Dates", " = '" & !! & "' AND CVDate(Int( #" & & "# )) = #" & (Date) & "#")
I...
|
by: akirekab |
last post by:
I am using DCount, but I am not able to find how to set simple multiple criteria.
Here is sample of what i need.
=DCount("PatientProfileID","qryFaceToFaceReason_EAP_VG","FaceToFaceReasonID=2"...
|
by: iBasho |
last post by:
Hi
I am using the DCount function to check and alert users for existing IDs in my database after a new record is entered.
..Private Sub CARDID_AfterUpdate()
If DCount("*", "", " = '" & Me. &...
|
by: trinismooth |
last post by:
Hi All
Trying to do a dcount with multiple criteria for example: =DCount("","access rights"," = 'kev'" and ='Christian Fellowship members temp'" And ='yes'")
Thanks for any assistance
|
by: Manuel Baptista |
last post by:
Good afternoon,
I am attempting to count those records within a data base that do not match one variable answer in a given field.
To put it more clearly, I want to count the companies in a...
|
by: doma23 |
last post by:
Hi,
I have the following code that doesn't work:
DCount("*", "", _
"((='" & Form_frmMain.cmbField1 & "') AND (" & "='" & Form_frmMain.cmbField2 & "') AND (" & "='" &...
|
by: slenish |
last post by:
Hello All,
I am trying to use the DCount Function to count blank records on a table from a form. I want to make it so i can press a button and the function will run and show the total in a text...
|
by: DANNYOCEAN |
last post by:
I have a code that sucessfully calculates how many numbers are lower than for example 45 , for large sample number list.
But i´m looking for a code that have a COUNT function with MULTIPLE...
|
by: BHudson |
last post by:
Can someone help me. why does my dcount function on a text box in a report gives me a zero count when there is clearly 2 employee records in the query that has the number 1 in the heart trainee...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |