Code Help for Date Range Report

Hi,

Yes I have done that, but it doesn't seem to have solved my issue. Even in the one you sent back to me, it still lists EVERY visit. Whilst this is good for the detailed report, it isn't for the basic report.

I am sorry to be a pain, but as you can probably tell I am quite new to access and have created this database using a hodge-podge of stuff I found on google, and from the massive help from people like yourself on forums. Which means that when I hit a snag, I struggle to fix it without help-for-dummies like instructions.

I have found an old version that works how I want it to. Could you please have a look at the Basic Hours Report and see what I may have changed to stop it grouping the visits together?

In the attached database, the record for "Steven Journeaux" on the basic hours report shows 5 visits and the total hours from those 5 visits, without listing them all.

I have no idea how to get my current report back to that, whilst making it work with the new date range drop down form.
 

Attachments

Yes I have done that, but it doesn't seem to have solved my issue. Even in the one you sent back to me, it still lists EVERY visit. Whilst this is good for the detailed report, it isn't for the basic report.

When I run the report from the frmWhatDates form in the database I attached earlier, I get a summary report (see attached) not a detail report. I'm not sure why you are not getting the same. Which form are you opening?


I have found an old version that works how I want it to. Could you please have a look at the Basic Hours Report and see what I may have changed to stop it grouping the visits together?

You cannot use the old version and filter by visitdate since in the old version you used an aggregate query. If you bring the visit date into that aggregate query, it would no longer function as an aggregate query and thus you would not get the count of visits by client nor the total of the time.
 

Attachments

I copied the reports and the frmWhatDates form into my database, and you are right, it does now show all clients as a summary.

However, now the total hours are wrong. Your basic report just totals the minutes. In my original one, I used a formula like this:

=IIf((Text13.Value\60)<10,"0"+CStr(Text13.Value\60),CStr(Text13.Value\60))+":"+IIf((Text13.Value Mod 60)<10,"0"+FormatNumber(Text13.Value Mod 60,0),FormatNumber(Text13.Value Mod 60,0))
It seems yours just has the equivilent to the Text13 box as used above, so I have added a field with this code and it seems to work.

However, I have 2 further issues:

1) In your basic report, it counts the number of visits, but for clients with 0 visits, it shows a value of 1. How can I get this back to showing a 0 when a client has no visits against the record?
2) How can I order the basic report by LogNo instead of client name? I have a LogNo field in both Visits and Clients tables, so I am also unsure which one I should be using (they are linked).
 
Sorry, scratch point 2, I just added a sort on LogNo from Client table and that has worked.

Still struggling with point 1) though
 
Last edited:
Sorry about that, the control is counting client names. Go to the report design and find the control ( it should say count([Client Name]). You will need to change it to: =Count([Visits.LogNo])
 
Ah, that has worked like a charm.

I can't thank you enough for your help getting this fixed. I was completely at a loss as to how to get it to do what I wanted to, and you have done it all.
 
You're welcome & good luck with your database.
 

Users who are viewing this thread

Back
Top Bottom