Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Easy Points: Hospital DB Alerts.

Posted on 2005-03-17
13
Medium Priority
?
393 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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

572 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