Multiple Criteria in DCount Function

[Eman1234]

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

[PhilOfWalton]

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

[Eman1234]

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

[PhilOfWalton]

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

[Eman1234]

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

[PhilOfWalton]

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

[Eman1234]

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

[PhilOfWalton]

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

[Eman1234]

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

[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.

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