?
Solved

Easy Points: Hospital DB Alerts.

Posted on 2005-03-17
13
Medium Priority
?
392 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:A-G-Lee-T
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 18

Assisted Solution

by:Billystyx
Billystyx earned 800 total points
ID: 13572340
you would be best using either an autoexec macro that starts at open of the db to call an sql query (with criteria as Next Appointment Between Date and Date+1), or onload of the startup form (in the vba screen) docmd.openquery "yourqueryname"

Billystyx
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13572490
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")

 
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13572499
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:mingfattt
ID: 13573101
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.
0
 
LVL 2

Expert Comment

by:mingfattt
ID: 13573127
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
0
 
LVL 2

Expert Comment

by:mingfattt
ID: 13573138
well you can show the name by a bit of edit in select also... good luck hope it helps
0
 
LVL 2

Expert Comment

by:mingfattt
ID: 13573201
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
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13573343
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
0
 
LVL 2

Accepted Solution

by:
mingfattt earned 1200 total points
ID: 13587878
If consider about today and tomottow just make it in this way

SELECT ID,Name, Format([Testing].[Date],'mm/dd/yyyy') AS Appointment_Date
FROM tblAppointments
WHERE (((Format([tblAppointments].[Date],'mm/dd/yyyy'))=Format(Now(),'mm/dd/yyyy'))) OR (((Format([tblAppointments].[Date],'mm/dd/yyyy'))=Format(Now()+1,'mm/dd/yyyy')));

Good Luck
0
 
LVL 2

Expert Comment

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

Me, Yee
0
 
LVL 1

Author Comment

by:A-G-Lee-T
ID: 13609341
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.
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13609388
no worries, glad to help, thanks for splitting points and good luck with it:)
0
 
LVL 2

Expert Comment

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

Me, Yee
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question