By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Multiple Criteria in DCount Function

P: 12
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
Jan 22 '18 #1

✓ answered by PhilOfWalton

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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TbContacts ( ContactName )
  2. SELECT DISTINCT Mike.From
  3. FROM Mike;
  4.  
And this the query to append the messages
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TbdMessages ( ContacID, Subject, DateReceived )
  2. SELECT TbContacts.ContactID, Mike.Subject, Mike.Received
  3. FROM Mike INNER JOIN TbContacts ON Mike.From = TbContacts.ContactName;
  4.  
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Dim Fltr As String
  5.  
  6. Private Sub ApplyFilt_Click()
  7.  
  8.     Dim FDate As Date
  9.     Dim TDate As Date
  10.  
  11.     If IsNull(FromDate) And IsNull(ToDate) Then
  12.         If Nz(CboContact) > 0 Then
  13.             Fltr = "ContactID = " & CboContact
  14.         End If
  15.         GoTo FilterIt
  16.     End If
  17.  
  18.     If IsNull(CboContact) Then
  19.         If Not IsNull(FromDate) Then
  20.             FDate = CDate(Format(FromDate, "mm/dd/yy"))
  21.             If IsNull(ToDate) Then
  22.                 TDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  23.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  24.                 Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
  25.             Else
  26.                 TDate = DateAdd("d", 1, ToDate)          ' Add 1 day
  27.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  28.                 Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
  29.             End If
  30.         End If
  31.         GoTo FilterIt
  32.     Else
  33.         If Not IsNull(FromDate) Then
  34.             FDate = CDate(Format(FromDate, "mm/dd/yy"))
  35.             If IsNull(ToDate) Then
  36.                 TDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  37.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  38.                 Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
  39.             Else
  40.                 TDate = DateAdd("d", 1, ToDate)          ' Add 1 day
  41.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  42.                 Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
  43.             End If
  44.         End If
  45.         GoTo FilterIt
  46.     End If
  47.  
  48.  
  49. FilterIt:
  50.     Me.Filter = Fltr
  51.     Me.FilterOn = True
  52.  
  53.     RecCount = Me.RecordsetClone.RecordCount
  54.  
  55. End Sub
  56.  
  57. Private Sub Form_Load()
  58.  
  59.     RecCount = Me.RecordsetClone.RecordCount
  60.  
  61. End Sub
  62.  
  63. Private Sub FromDate_AfterUpdate()
  64.  
  65.     If IsNull(ToDate) Then
  66.         ToDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  67.         ToDate = DateAdd("s", -1, ToDate)           ' Take off 1 second to return toi previos day
  68.     End If
  69.  
  70. End Sub
  71.  
  72.  
Phil

Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jan 22 '18 #2

P: 12
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
Jan 23 '18 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Jan 23 '18 #4

P: 12
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
Jan 23 '18 #5

PhilOfWalton
Expert 100+
P: 1,430
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
Jan 23 '18 #6

P: 12
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
Jan 23 '18 #7

PhilOfWalton
Expert 100+
P: 1,430
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
Jan 23 '18 #8

P: 12
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
Jan 23 '18 #9

PhilOfWalton
Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TbContacts ( ContactName )
  2. SELECT DISTINCT Mike.From
  3. FROM Mike;
  4.  
And this the query to append the messages
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TbdMessages ( ContacID, Subject, DateReceived )
  2. SELECT TbContacts.ContactID, Mike.Subject, Mike.Received
  3. FROM Mike INNER JOIN TbContacts ON Mike.From = TbContacts.ContactName;
  4.  
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Dim Fltr As String
  5.  
  6. Private Sub ApplyFilt_Click()
  7.  
  8.     Dim FDate As Date
  9.     Dim TDate As Date
  10.  
  11.     If IsNull(FromDate) And IsNull(ToDate) Then
  12.         If Nz(CboContact) > 0 Then
  13.             Fltr = "ContactID = " & CboContact
  14.         End If
  15.         GoTo FilterIt
  16.     End If
  17.  
  18.     If IsNull(CboContact) Then
  19.         If Not IsNull(FromDate) Then
  20.             FDate = CDate(Format(FromDate, "mm/dd/yy"))
  21.             If IsNull(ToDate) Then
  22.                 TDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  23.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  24.                 Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
  25.             Else
  26.                 TDate = DateAdd("d", 1, ToDate)          ' Add 1 day
  27.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  28.                 Fltr = "DateReceived Between #" & FDate & "# And #" & TDate & "#"
  29.             End If
  30.         End If
  31.         GoTo FilterIt
  32.     Else
  33.         If Not IsNull(FromDate) Then
  34.             FDate = CDate(Format(FromDate, "mm/dd/yy"))
  35.             If IsNull(ToDate) Then
  36.                 TDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  37.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  38.                 Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
  39.             Else
  40.                 TDate = DateAdd("d", 1, ToDate)          ' Add 1 day
  41.                 TDate = CDate(Format(TDate, "mm/dd/yy"))
  42.                 Fltr = "ContactID = " & CboContact & " AND DateReceived Between #" & FDate & "# And #" & TDate & "#"
  43.             End If
  44.         End If
  45.         GoTo FilterIt
  46.     End If
  47.  
  48.  
  49. FilterIt:
  50.     Me.Filter = Fltr
  51.     Me.FilterOn = True
  52.  
  53.     RecCount = Me.RecordsetClone.RecordCount
  54.  
  55. End Sub
  56.  
  57. Private Sub Form_Load()
  58.  
  59.     RecCount = Me.RecordsetClone.RecordCount
  60.  
  61. End Sub
  62.  
  63. Private Sub FromDate_AfterUpdate()
  64.  
  65.     If IsNull(ToDate) Then
  66.         ToDate = DateAdd("d", 1, FromDate)          ' Add 1 day
  67.         ToDate = DateAdd("s", -1, ToDate)           ' Take off 1 second to return toi previos day
  68.     End If
  69.  
  70. End Sub
  71.  
  72.  
Phil
Jan 24 '18 #10

P: 12
Thank you so much for your help Phil and taking the time to do this.

-Eric
Jan 24 '18 #11

Post your reply

Sign in to post your reply or Sign up for a free account.