At present, because you are using Boolean OR operations, rows will be updated
where the date difference is within 30 days for any of the three criteria.
Are you saying that if one of the criteria falls outside the 30 day
difference than you don't want that row updated, even if another falls within
it? If so then you would need to use Boolean AND operations, but also handle
any Nulls as comparing a Null to anything evaluates not to True or to False,
but to Null. So the query would be:
UPDATE tbl_NewPatientInfo INNER JOIN tbl_TEPRecords
ON tbl_NewPatientInfo.[MDACC#] = tbl_TEPRecords.[MDACC#]
SET tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE
((DateDiff("d",[tbl_NewPatientInfo].[Date 1st TEP],[Date])<30
AND DateDiff("d",[tbl_NewPatientInfo].[Date 1st TEP],[Date])>=0)
OR [tbl_NewPatientInfo].[Date 1st TEP] IS NULL)
AND
((DateDiff("d",[tbl_NewPatientInfo].[Date 2nd TEP],[Date])<30
AND DateDiff("d",[tbl_NewPatientInfo].[Date 2nd TEP],[Date])>=0)
OR [tbl_NewPatientInfo].[Date 2nd TEP] IS NULL)
AND
((DateDiff("d",[tbl_NewPatientInfo].[Surgery date],[Date])<30
AND DateDiff("d",[tbl_NewPatientInfo].[Surgery date],[Date])>=0)
OR [tbl_NewPatientInfo].[Surgery date] IS NULL);
Note that the parentheses in the above are important to ensure the correct
order of evaluation of the various expressions. Access puts in unnecessary,
albeit harmless, extra parentheses when a query is constructed in design
view. I've included only those which are necessary in the above. The use of
carriage returns on the other hand is purely to enhance the readability of
the SQL, and has no bearing on its execution.
What the above should do is update a row where at least one of the three
criteria falls within the 30 days but none fall outside the 30 days, either
by virtue of falling within the 30 days or the TEP or surgery date not having
a value entered, i.e. being Null. That's the only other interpretation I can
see other than that of your current query, that any of the three criteria
fall within the 30 days.
BTW Date is not a good choice for a field name as it’s the name of a built
in function. Wrapping it in square brackets [Date] when you refer to it
should avoid any confusion, but its nevertheless better to use a more
specifically descriptive field name as you've done for the TEP and surgery
date fields.
Ken Sheridan
Stafford, England
Radhika said:
Sorry for the ambiguity. I want 'ReasonForReferral' to be selected as
'Initial Fit', if the date difference is between 0 and 30. Therefore, I want
to include rows with dates on [Date] and 30 days before [Date] but not later.
I have some TEP and surgery dates that are later than [Date] and they give me
negative values for the date difference, e.g. -1000. I do not want such date
difference values to update the 'ReasonForReferral' field. However, this is
what is happening. I tried replacing >0 with >-1, but that gives me the same
results.
[Date] is the name of a field.
I am not sure about whether there is a problem with my SQL or whether there
is another issue?
Thank you!
Radhika
Ken Sheridan said:
Radhika:
Anything less than 30 is also greater than 0 unless its 0 or less, so I'm
not sure what you mean when you say 'not those >0'.
In effect you are asking with each criterion for rows where the difference
between the TEP or surgery date and the value of [Date] (is this a
parameter?) is between 1 and 30 days, i.e. the TEP or surgery date is less
than 30 days before [Date], but not [Date] or later. If you want to also
include those with dates of [Date] or later, then you don't need to test for
0 at all. If you want to include rows with dates on [Date] but not later,
then test for >-1.
Ken Sheridan
Stafford, England
Radhika said:
I have the following SQL for my update query:
UPDATE tbl_NewPatientInfo INNER JOIN tbl_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = tbl_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st TEP],[Date]))<30 And
(DateDiff("d",[tbl_NewPatientInfo].[Date 1st TEP],[Date]))>0)) OR
(((DateDiff("d",[tbl_NewPatientInfo].[Date 2nd TEP],[Date]))<30 And
(DateDiff("d",[tbl_NewPatientInfo].[Date 2nd TEP],[Date]))>0)) OR
(((DateDiff("d",[tbl_NewPatientInfo].[Surgery date],[Date]))<30 And
(DateDiff("d",[tbl_NewPatientInfo].[Surgery date],[Date]))>0));
For some reason, it is updating only the those <30, but not those >0. What
am i doing wrong?
Thank you,
Radhika
