Update SQL for timestamp only treatments that are ready (1 Viewer)

Thicko

Registered User.
Local time
Today, 09:28
Joined
Oct 21, 2011
Messages
61
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.

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
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:

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
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
 

sneuberg

AWF VIP
Local time
Today, 02:28
Joined
Oct 17, 2014
Messages
3,506
I would guess the Or is causing your problem. And has a higher precedence than Or so usually parenthesis are needed, e.g. if you have

A And B Or C And D

you probably want

A And (B Or C) And D

I suggest take out the parenthesis Access puts in and rewrite the WHERE clause with parenthesis where they are really needed.
 

Thicko

Registered User.
Local time
Today, 09:28
Joined
Oct 21, 2011
Messages
61
Many Thanks Steve,

You where right the OR was causing the problem so instead of:

((A And B) Or C) And D And E And F

it's now

(A And B And D And E And F) Or (C And D And E And F)

Working with dates and times got me so confused I couldn't see the issue.
 

Users who are viewing this thread

Top Bottom