Returning Records with a Certain Date from a DCount Function

[Tezcatlipoca]

I'm not too sure if this is the right way to go about this, but I have a form - frmReports - on which is a list box, showing all the database members. When I click on any member, an AfterUpdate event sends that member's number to a hidden unbound textbox, Number. This is then used in a number of areas around the form to generate statistics. For example, a visible textbox on the form contains the ControlSource code:

=DCount("[ID]","tblLogs","[MemberNumber] = '" & [Number] & "'")

so it shows the total number of contacts by whichever member is selected from the list box.

All this works great. However, just make like more complicated, I now want to add the ability to filter the result by the date the record was logged.

I have a table of records, tblLogs, consisting of the fields ID (primary key), MemberNumber and CallLog. CallLog is a UK format General Date (so time and date), and gets added everytime a new record is created.

On my form, there are two textboxes (txtStartDate and txtEndDate). Each has a calendar button next to it, which can be used to fill out the boxes in UK Short Date format.

Soo, after that lengthy explaination, I'm now trying to edit the above code so it takes into account these two dates, and only counts up records between them. I tried this:

=DCount("ID", "tblLogs",""[MemberNumber] = '" & [Number] & "' AND [CallLog] >=Forms!frmReports![txtStartDate] AND <=Forms!frmReports![txtEndDate]")

but it just returns a syntax error. I'm also not entirely sure whether this is the right approach at all, given that the CallLog stamp is in General Date (so time and date) format, and the two textboxes are in Short Date formats.

Can anyone assist?

 

[stopher]

The expression you need is:

=DCount("ID","tblLogs","[MemberNumber] = '" & [Number] & "' AND [CallLog] BETWEEN #" & Forms!frmReports!txtStartDate & "# AND #" & Forms!frmReports!txtEndDate & "#")

Note the use of # round the dates and BETWEEN as an operator.

hth
Chris

 

[Tezcatlipoca]

Thanks, stopher for that code, and for your advice on how to combat the General Date to Short Date issue. However, I've run into an odd issue and can't seem to resolve it.

Take a look at the attached example, which is a very cut down copy of the database.

If you set the dates to 11/12/2008 and 21/12/2008, the records seem to filter. Paul Atreides (Member 029544), for example, shows 2 calls between these dates. If you set the start date to much earlier - say 01/10/2008 - that same member shows 5 calls, which is the total logged for him...

...the problem is that he didn't make 2 calls in December, so I can't understand why 2 is being returned. Clearly the code thinks it's filtering something; I just can't see what!

 

[stopher]

The problem is due to the fact that Access often expects the date to be in USA date format. So if you don't live in the USA then you need to present it in US format. Use the following:

=DCount("ID","tblLogs","[MemberNumber] = '" & [Members] & "' AND [UserName]<>'' AND [CallLog] BETWEEN #" & Format(Forms!frmReports!txtStartDate,"mm/dd/yy") & "# AND #" & Format(Forms!frmReports!txtEndDate,"mm/dd/yy") & "#")

As an aside, note that your Call Log dates include times. So if you choose 1st Nov-11th Nov, then you will only seem records from 1st Nov until midnight on 10th Nov.

Chris

 

[Tezcatlipoca]

The problem is due to the fact that Access often expects the date to be in USA date format. So if you don't live in the USA then you need to present it in US format.

Ah, thanks. I thought that might have been the issue, but as my StartDate, EndDate and the dates logged in the table were all in UK format, I disregarded it as being responsible, assuming - perhaps foolishly - that Access would realise the difference and amend itself accordingly.

As an aside, note that your Call Log dates include times. So if you choose 1st Nov-11th Nov, then you will only seem records from 1st Nov until midnight on 10th Nov.

Ah, interesting to note, thanks. Whilst it is essential the time is logged as well (for use elsewhere in the database), knowing how Access filters these records means I can update the relevant help menus.

Thanks for all your help, Chris; it's very much appreciated.