473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Multiple Criteria in DCount Function

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

10 3542
PhilOfWalton
1,430 Expert 1GB
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
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
1,430 Expert 1GB
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
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
1,430 Expert 1GB
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
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
1,430 Expert 1GB
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
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
1,430 Expert 1GB
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
Thank you so much for your help Phil and taking the time to do this.

-Eric
Jan 24 '18 #11

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

Similar topics

1
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),...
3
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...
1
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"...
1
iBasho
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. &...
2
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
1
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...
12
doma23
by: doma23 | last post by:
Hi, I have the following code that doesn't work: DCount("*", "", _ "((='" & Form_frmMain.cmbField1 & "') AND (" & "='" & Form_frmMain.cmbField2 & "') AND (" & "='" &...
3
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...
4
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...
2
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
1
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...
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.