Hi Folks,
We have a tracking system for chemotherapy when all the treatments for that day are ready an UPDATE SQL query is run to set the status of all patients drugs to ready for collection.
This has worked fine too date. However, now we are making some treatments the day ahead and to distinguish treatments made today for today, from treatments made today for tomorrow, the time for treatments made today for tomorrow is adjusted to be > 17:00.
Now when the above SQL is run it sets all treatments made that day to ready for collection, I need this to only set treatments with a TreatmentTime of <17:00 to ready (i.e only treatments for today).
I've modified the SQL to do this:
My problem now is that when treatment is made the day before and scanned on the on the day only one treatment is timestamped as ready for collection and not all treatments for that day.
Terribly confused and Any Help is appreciated
Thicko
We have a tracking system for chemotherapy when all the treatments for that day are ready an UPDATE SQL query is run to set the status of all patients drugs to ready for collection.
Code:
ReadyForCollectionSQL = "UPDATE tblChemoRecord SET tblChemoRecord.ReadyForCollection = Now() "
ReadyForCollectionSQL = ReadyForCollectionSQL + "WHERE ((((tblChemoRecord.Date)=#" & Format([Forms]![frmTextConfirmation].[ConfirmDate], "yyyy-mm-dd") & "#) OR ((tblChemoRecord.Date)=#" & Format([Forms]![frmTextConfirmation].[ConfirmDate] - 1, "yyyy-mm-dd") & "#)) AND ((tblChemoRecord.Patient)='" & [Forms]![frmTextConfirmation].[ConfrimPatient] & "') AND ((tblChemoRecord.[Patient Number])='" & [Forms]![frmTextConfirmation].[ConfrimPatientNumber] & "') AND ((tblChemoRecord.PatientDateOfBirth)=#" & Format([Forms]![frmTextConfirmation].[ConfirmPatientDateOfBirth], "yyyy-mm-dd") & "#)) "
DoCmd.RunSQL ReadyForCollectionSQL
Now when the above SQL is run it sets all treatments made that day to ready for collection, I need this to only set treatments with a TreatmentTime of <17:00 to ready (i.e only treatments for today).
I've modified the SQL to do this:
Code:
ReadyForCollectionSQL = "UPDATE tblChemoRecord SET tblChemoRecord.ReadyForCollection = Now() "
ReadyForCollectionSQL = ReadyForCollectionSQL + "WHERE ((((tblChemoRecord.Date)=#" & Format([Forms]![frmTextConfirmation].[ConfirmDate], "yyyy-mm-dd") & "# AND TimeValue(tblChemoRecord.TreatmentTime)<#5:00:00 PM#) OR ((tblChemoRecord.Date)=#" & Format([Forms]![frmTextConfirmation].[ConfirmDate] - 1, "yyyy-mm-dd") & "#)) AND ((tblChemoRecord.Patient)='" & [Forms]![frmTextConfirmation].[ConfrimPatient] & "') AND ((tblChemoRecord.[Patient Number])='" & [Forms]![frmTextConfirmation].[ConfrimPatientNumber] & "') AND ((tblChemoRecord.PatientDateOfBirth)=#" & Format([Forms]![frmTextConfirmation].[ConfirmPatientDateOfBirth], "yyyy-mm-dd") & "#)) "
DoCmd.RunSQL ReadyForCollectionSQL
Terribly confused and Any Help is appreciated
Thicko