asked on

Easy Points: Hospital DB Alerts.

Hey guys.

I need help with my homework.

I created an Access DB for a hospital, with one table containing a list of patients and their personal and medical data. One of the columns stores the date for the next programmed medical appointment for each one of the patients.

Example:

ID Name Next Appointment
5550804 John Doe 3/27/2004

What I need is, when the DB is open I should be advised if there is any appointments programmed for today or tomorrow. Simple as that.

I'm not much experienced in MS Access, but I know a lot about programming for Excel (VBA) so that might be a help. I need a hint about what would be the best way of having those alerts working.

Thanks in advance.

SOLUTION

Billystyx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjafferr

On your Form creat 2 Textboxes, call the first [Todays] and the second one [Tomorrows],
in the Control Sourse of [Today] have this:
=DCount("*","Yout Table Name","[Next Appointment]=date()")

in the Control Sourse of [Tomorrows] have this:
=DCount("*","Yout Table Name","[Next Appointment]=date()+1")

OR you can do this On Load of the Form:
[Todays]=DCount("*","Yout Table Name","[Next Appointment]=date()")
[Tomorrows]=DCount("*","Yout Table Name","[Next Appointment]=date()+1")

Billystyx

That would work too, but the reason I suggested a query was that it seems not much use to know how many appointments there are if you don't know who they're for (even though that's what agleet asked for).

Billystyx

mingfattt

In your Query, you open one new query with below script:
SELECT Format(Testing.Date,'mm/dd/yyyy') AS Expr1
FROM Testing
WHERE (((Format([Testing].[Date],'mm/dd/yyyy'))=Format(Now(),'mm/dd/yyyy')));

Then in your form, you just use the query for the POP up.

mingfattt

meanwhile in the form (in case u want it) u should write in this way,

Private Sub Command0_Click()

DoCmd.OpenQuery "your query name", acViewNormal, acEdit

End Sub

mingfattt

well you can show the name by a bit of edit in select also... good luck hope it helps

mingfattt

or else u can make it this way, try to create a form and make it the as a pop up form which you can set it in the menu bars tools---> start up and then put the above code in the on load of the form this is the easiest way i can find out... good luck

Billystyx

Your query needs to have a Between criteria, not an = criteria.
like:
SELECT tblAppointments.Date1, tblAppointments.LastName
FROM tblAppointments
WHERE ((tblAppointments.Date1) Between ((Date()) And (Date() +1)));
or something like that

Billystyx

ASKER CERTIFIED SOLUTION

mingfattt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mingfattt

Well, Thanx anyway for your point, both of us learn.... since i am very new here... have a pleasent day

Me, Yee

A-G-Lee-T

ASKER

Thank you so much!

You saved my life guys, I had to edit the scripts in order to apply them to my DB, but I can't believe how flawlessly they worked.

Mingfattt gave me the closest approach to my query, but as I finally applied things Billystyx was a lot helpful too, so that's why I splitted points that way and I hope it was fair for you both.

Thanks again

See you.

Billystyx

no worries, glad to help, thanks for splitting points and good luck with it:)

mingfattt

Billystyx and A-G-Lee-T still a lot to learn for both of you thanx guys... bye

Me, Yee