Hi i have 31 combo boxes on a report, names 1st 2nd etc for days of the month can anyone give me the proper syntax to total up all the boxes in a report i just cant get it to do it

Hi -

31 combo boxes in a report? Perhaps if you'd explain a little more what you're trying to accomplish.

Bob

its a scheduling / invoice report ....i had to add the combo boxes as putting in the various dates for the month for each client would prove very time consuming .. if i can total these combo boxes it will do exactly what they want it to do ( nevermind how wacky ) just cant work the syntax out . each combo box can contain 0.25 to 10 and i just need them totalling

Hi -

Still can't understand putting a combo box in a report. Do you perhaps mean a form (where'd you able to manipulate the combo box)?

Bob

they are on the form they just appear as combos on the reports thats all

In your form's query, add a calculated field (assuming your combo boxes are named [Day1] thru [Day31].
Total: [Day1]+[Day2]+[Day3]+[Day4]+[Day5]+[Day6]+[Day7]+[Day8]+[Day9]+[Day10]...[Day31]

Add a text box to your report, with Control Source: Total

Consider converting your report's combo boxes to text boxes since they serve no purpose other than to take up space.
Combo boxes are intended to allow users to make choices--great on a form, useless and very likely unworkable on a report.

Bob

cheers will give it a go many thanks

wel i have given it a go and im going to have to admit defeat on this one if anyone knows how i can do this ( i have no knowledge of VB at all ) it would be greatly appreciated . The problem with adding up in the previous solution by raskew is that i have a field for each day and the query is adding the total amount of hours for all clients on that day .

i have 2 tables one with patient details and one that should store visit information for the days in that month . most of the visit information will not change from month to month apart from cancellations or additonal hours . so basically the report will be the same hours just different months
visits are X hrs per day no time information at all .

the report needs to list all days of the month even if there have been no visits at all , and total them up for the month .
unless we can work out how to add the filds in the report this is

i was asked to do this as a favour and since they are non profit i thought i would do a good deed .....can anyone help me with this one ? Please aaaaaaahhhh ;-)

I did tell you before, you don't need thirty one text boxes, it's not that difficult to create a temporary list of days in any month and then to use a Union or Crosstab query to bring it all together

ok union and crosstab never been there....im totally in the dark here. i didnt think it would be so difficult ....hence my first version that you manually entered the date of the visit into, this worked untill the next month where the user had to change 30/31 dates for each patient . ;-)

Surely they should only enter dates that are relevant?

they should yes but even then they would have to forward the dates to the next month ..this is why i tried the pull down idea ....... this data does not even have to be recalled ...it just needs to print out every month and be moved on one month ( this is why i thought it would be doable ) .....all of the dates for the month are printed and some are empty of hours and some have data against them ..........odd i know ....im totally stumped now , ;-)

So are you saying that the same patients visit each month?

Hi -

A Rant--

I often wish that developers who pride themselves on theoretically correct programming skills could be offered the opportunity to spend a few days inputting data using their squeaky-clean techniques, in a high-speed environment with a deadline hanging over their heads. In my experience they produce technically correct 'clunkers'!

Examples:

Calendar Controls. They look gee-whiz and work great if you’re working with the current or previous month. Conversely, they absolutely suck if one is required to input the date of birth (DOB) of a seventy-two year old nursing home patient. Clickety-Clickety-Clickety gets old real fast, while a formatted text box works just really fine, in a fraction of the time.

Combo Boxes. Great for restricting the user’s options but absolutely hideous if the user is required to choose a day between 1 and 31. Provided the user knows the potential options, a formatted text box improves speed and accuracy many-fold.

Normalization and Spreadsheet Mentality. In MHO, there’s a place for a non-normalized solution. Take this thread: Imagine a scenario where a patient/client is receiving both residential and non-residential services. Residential services are a piece of cake: client is admitted on x date, discharged on y date—a simple DateDiff() equation sorts that out with a separate record for each day of their stay. If, however, the client is also receiving non-residential services, from multiple caregivers, on an irregular basis, then a subform linked to the client, with caregiver combo, service combo, and Days 1 – 31 (each with a default value of 0) makes perfect sense. The user can input the mm/yyyy, caregiver and service code and then skim across the form adding service units where needed, with a calculated totals field to show the number of service units provided during the mm/yyyy. Denormalized, you betcha, but it takes very little programming skill to convert this denormalized record into a fully normalized table in a matter of seconds, disregarding the unused dates. Consider the ‘normalized’ option: Enter the Caregiver, Service, and a date, then the number of units. Then do it again for the next date, and again and again and again. Don’t know about you, but that doesn’t sound like a ‘good deal’ to me.

End of Rant!

Bob

So are you saying that the same patients visit each month?

The majority of the patients either have the same amount of hours per day on the same days ( or day intervals) each month so apart from amendments or cancellation the reports would be the same just the month would be different .

raskew I must agree that usabilty has to be a fundamental feature of any solution designed . The problem i had was that they required to have all of the months dates listed , even if the patient was not visited . This in turn had its own problems with data input. So in my mind i thought that having the days listed with combo boxes for the hours would be a solution, due to my ignorance i found a whole other set of problems . So thats 1-0 to the practice of planning the job beforehand .

I'm sure Pat Hartman posted an example here of entering data in a spreadsheet like manner, have a look in the samples forum, it might give you some ideas

i did have my subform in datasheet view in version 1 this did work untill all the dates had to be cleared and new dates for each client entered the beggining of the month. I was trying to avoid that as it would be just as time consuming as the present 'by hand method ' if you have any theories on how it should be done im willing to scrap it all and start again ;-)

'The majority of the patients' do this or that. The problem is that not all of the patients do this this or that. Result: You're going to have examine the status of each patient, each month. Give up on the idea that you can duplicate a patient's previous month's results. It's not going to fly without examination of this month's situation.

Bob

i would have though that you would only have to change each months details if they have cancelled an appointment or increased their hours. the previous months totals would not have to be kept, as records will be kept on hard copy.

I.E if joe bloggs gets a visit of an hour every other day and his situation hasnt changed his report would be the same apart from the month.

or am i going insane ;-)

Yes i am going insane ...of course the day of the visit would change as the months progressed .....ok double stumped now anyone any idea how i could approach this even ?

Hi -

What about the 'minority' of patients that don't fit this mold? What you're suggesting (duplicating the previous month's data) is going to lead to gross errors, guaranteed.

Bob

maybee not duplicating the previous months data, but i am at a loss on how to clear the current months dates and insert the next months dates with least amount of input by the user

but of course it would mean still going back through each client and entering their new hours per day at the start of each month ohhhh my head hurts now i know whay im a designer lol

ok im going to throw this open as the more i think about it the more difficult this is getting http://gcommercial.co.uk/cymorth2003.zip

if someone could have a go with it the form with the problems is clients1

here the hrs for the month will need to be entered but the report will need to show all dates for the month even if thay have no visit on that day .

the point is to populate the months dates in the date cloumn

anyone pls ?

Dan -
The problem i had was that they required to have all of the months dates listed , even if the patient was not visited.

Why, that doesn't make sense. Take a look at your credit card bill. If you don't have any charges in July, you're not going to see a billing set to 0.

...but i am at a loss on how to clear the current months dates and insert the next months dates with least amount of input by the user.

You don't clear a previous month's dates and add the current month's dates. Conversely, you add a new record for the current month (provided there's billing for the current month) and fill it out appropriately. In my experience (and we do it every month) provided you have legible input to work from, you're talking seconds per record. You're creating a historical record of patient billing. In your subform, you need to add a new record or records only if the patient had billing during that month. No billing, no record. If there is billing, then a new record with the default of Day1 thru Day31 set to 0. Then you only need to add the mm/yyyy, caregiver and billing code, then skim across the form adding units only where there were units, the rest of the days are already set to 0.

Once we've got that concept down, then it's a simple matter to add your billed days (the ones > 0) to a normalized table.

Bob

Pat-

Rich says: I'm sure Pat Hartman posted an example here of entering data in a spreadsheet like manner, have a look in the samples forum, it might give you some ideas

Can you give us a hand here?

Best wishes, Bob

p.s.: Rich, with your 13,000+, mostly unhelpful solutions, why don't you point to it?

Rich -

Are you there, big guy? You obviously weren't in favor of my solution. So, show us yours, in a little more detail than ' I'm sure Pat Hartman posted an example ...."

Were I a critical person (perish the thought), I'd might be prone to say that's a really wimpy, no-brainer response designed to push-up one's post count without providing any real information.

What do you think, big guy?

Best Wishes - Bob

Don't take your frustration out on me Robert, either I'm mad and the example never existed or it got lost when the forum got hacked, but I still swear it was there

Rich,

Hmmm?! Nice try. Perhaps if you couldn't find the mysterious post, you could explain it yourself. What do you think, big guy?!

Bob

This might be what Rich is refering to.
I had it in an old directory.
I think there are some credits within the code and forms.
Suspect it is the db originally posted by Pat.

James.

Rich,

Hmmm?! Nice try. Perhaps if you couldn't find the mysterious post, you could explain it yourself. What do you think, big guy?!

Bob
Why shoot the messenger Robert? I never said I disagreed with your solution, nor did I say there was a better way, I don't nor ever have entered data in a spreadsheet manner in Access.
I did download an example from somewhere many moons ago but all I remember was that there was a lot of code involved.
I might, and it's a big might, have a copy of it buried somewhere but you'll have to be patient for the while

I am coming back to this post because I found another db with an example of appointments and a good means of displaying them with a calendar form.

It comes from here (named "calendar"):
http://www.utterangel.com/pages/access_downloads.aspx#pagetop

I have zipped and attached the db with updated records so that it doesn't bug-out due to old data.