#Error in text field on report

[kevin_maher]

Hi all

I've struck an issue which has me stumped, so am hoping you good folk out there can help me, please....

I work for a training organisation which trains the people that work in kindergartens, child care centres and the like. One of our staff visit the centres on a regular basis and lets me know each day the centres she visited, plus a couple of other things that aren't relevant to this problem.

I have a Visits table where the visits are stored (ID, Centre_ID, Visit_Date ...) and one thing I am trying to do is create a report that will show, for a given time period (e.g. 1st of August to 31st of August):
a) how many centres were visited
b) on how many days visits took place so e.g. if she visited on the 26th, 27th, 28th and 29th, that would be 4 days, regardless of how many places she visited

On my report I have two text fields for (a) and (b) above. The report gets its data from
SELECT t_Visits.Centre_ID, t_Visits.Visit_Date
FROM t_Visits
GROUP BY t_Visits.Centre_ID, t_Visits.Visit_Date
HAVING (((t_Visits.Visit_Date) Between [Enter Start Date:] And [Enter end date:]));

The formula to generate a figure for each of the fields in the expression builder is:
- centres visited : =IIf([Report].[HasData],Count([Centre_ID]),0)
- days on which visits took place : =IIf([Report].[HasData],Count(Unique([Visit_Date])),0)

Both of those give a #Error after the dates have been entered. Could someone let me know why, please and how I can get it to do what I want it to do? I've had a look on the 'net for that error and it's saying one reason is that two things in the database are named the same, which as far as I know is not the case because I've given those text fields unique names. It took me a bit to create a query that would count the number of unique days and have been able to do that now, but haven't been successful in getting a variation of that query to work on the query that's behind this report, hence the Count(Unique()).

Let me know if you need any further information.

 

[arnelgp]

i think there is no Unique function in Access.

 

[The_Doc_Man]

Here is a list of the functions recognized in Access. "Unique" is not one of them.

Access Functions (by category) - Microsoft Support

Find Help with Access functions in this categorical list.

support.microsoft.com

If you want to count distinct values in a table, you need a query that does a "SELECT DISTINCT" in the table and then you could do a DCOUNT of the "helper" query based on whatever field defined the uniqueness.

 

[plog]

Neither Count nor Unique are functions available in a report. This is going to take more than one query and a better method for users to supply dates.

I would set up a form where user's select dates to use. When you do it via the query as you have done you're gonna get trash input (typos, invalid dates, end dates before start dates, etc.). Never trust users. Further, since its going to take multiple queries, all of them can reference the form where otherwise the user would have to enter the date ranges multiple times.

Then here's the queries to get how many centers visited:

SELECT t_Visits.Centre_ID
FROM t_Visits
GROUP BY t_Visits.Centre_ID
WHERE (((t_Visits.Visit_Date) Between Form![StartDate] And Form![End date]));

You would then do a DCOUNT to get the total number. This is the function you should use in the report instead of Count:

DCount Function - Microsoft Support

support.microsoft.com

Here's the queries to get the number of dates:

SELECT t_Visits.Visit_Date
FROM t_Visits
GROUP BY t_Visits.Visit_Date
WHERE (((t_Visits.Visit_Date) Between Form![StartDate] And Form![End date]));

Like unique centers you would use a DCount on the report and reference this query to get the total number of unique days.

 

[arnelgp]

maybe this demo will help you.

 

[arnelgp]

Neither Count nor Unique

Count() is a function.