none
Help needed to create a query RRS feed

  • Question

  • good day everybody

    I have a table which contains a patient_name, sick_type, entering_Date&time and leaving_Date&Time.

    I want to do a query that when I enter a date, it shows me all the sick people at that date and how many hours that patient is sick.

    For example, if patient A is entering hospital 1/15/2012 7:00 AM and leaving the hospital 1/17/2012 12:00 PM.
    So, if i enter the date as 1/16/2012, it shows me that the patient A is 24 hours as a sick
    and if I enter the date as 1/17/2012, it shows me that the patient A is 12 hours as a sick

    Can any body help me in that>>>

    Thanks

    I waiting for your great respond....
    Saturday, August 4, 2012 7:09 AM

Answers

  • I wouldn't use & in a field name, since & is also the concatenation operator.

    You could create a parameter query (I have assumed that the table is named tblVisits and that the & has been removed):

    PARAMETERS [Specify Date] DateTime;
    SELECT tblVisits.*, 24*(IIf(Nz([Leaving_DateTime],[Specify Date]+1)>[Specify Date]+1,[Specify Date]+1,Nz([Leaving_DateTime],[Specify Date]+1))-IIf(Nz([Entering_DateTime],[Specify Date]+1)<[Specify Date],[Specify Date],Nz([Entering_DateTime],[Specify Date]+1))) AS Duration
    FROM tblVisits
    WHERE (((24*(IIf(Nz([Leaving_DateTime],[Specify Date]+1)>[Specify Date]+1,[Specify Date]+1,Nz([Leaving_DateTime],[Specify Date]+1))-IIf(Nz([Entering_DateTime],[Specify Date]+1)<[Specify Date],[Specify Date],Nz([Entering_DateTime],[Specify Date]+1))))>0));

    Even better would be to create a form frmParameter with a text box txtDate (formatted as a date) in which the user can enter the date. The SQL then becomes:

    PARAMETERS [Forms]![frmParameter]![txtDate] DateTime;
    SELECT tblVisits.*, 24*(IIf(Nz([Leaving_DateTime],[Forms]![frmParameter]![txtDate]+1)>[Forms]![frmParameter]![txtDate]+1,[Forms]![frmParameter]![txtDate]+1,Nz([Leaving_DateTime],[Forms]![frmParameter]![txtDate]+1))-IIf(Nz([Entering_DateTime],[Forms]![frmParameter]![txtDate]+1)<[Forms]![frmParameter]![txtDate],[Forms]![frmParameter]![txtDate],Nz([Entering_DateTime],[Forms]![frmParameter]![txtDate]+1))) AS Duration
    FROM tblVisits
    WHERE (((24*(IIf(Nz([Leaving_DateTime],[Forms]![frmParameter]![txtDate]+1)>[Forms]![frmParameter]![txtDate]+1,[Forms]![frmParameter]![txtDate]+1,Nz([Leaving_DateTime],[Forms]![frmParameter]![txtDate]+1))-IIf(Nz([Entering_DateTime],[Forms]![frmParameter]![txtDate]+1)<[Forms]![frmParameter]![txtDate],[Forms]![frmParameter]![txtDate],Nz([Entering_DateTime],[Forms]![frmParameter]![txtDate]+1))))>0));


    Regards, Hans Vogelaar

    Saturday, August 4, 2012 10:59 AM