Serious Problem with a Date count fields & YES/No field

[Belinda]

I'm having a strange problem. Before importing my excel spreadsheet, I
ensured all the fields were properly typed, Date, Text, etc… I am having a
problem calculating a day count in a daysTilNext field that I would like to
keep track of difference in days of an inspectionDate and a dueDate. When I
run my query, (and I’ve tried it many ways with different SQL statements and
date functions), only two fields update properly out of 290+ records. You’ll
notice I’m having the same problem with another update query to check if the
inspection is due, overdue field, which again is only updating two records.
Example of query results:

ID equipId inspectionDate dueDate overDue daysTilNext
1 733-27 25-Jun-08 20-Jun-09 YES 360
2 A2L5FLL1 25-Jun-08 20-Jun-09 NO 365
3 A2L5FLL2 25-Jun-08 20-Jun-09 NO 365
4 A2L5FLL3 25-Jun-08 20-Jun-09 NO 365
5 A2L5GFI1 25-Jun-08 20-Jun-09 YES 360
6 A2L5GFI2 25-Jun-08 20-Jun-09 NO 365
7 A2L5GFI3 25-Jun-08 20-Jun-09 NO 365
8 A2L5GFI4 25-Jun-08 20-Jun-09 NO 365
9 A2L5GFI5 25-Jun-08 20-Jun-09 NO 365

My current SQL statements look like this:

SET main.daysTilNext = DateDiff("d",[inspectionDate],[dueDate]);

SET main.overDue = "YES"
WHERE (((main.dueDate)>=([inspectionDate]+100)));

I would appreciate any insight as to why I am experiencing this problem.

 

[Jerry Whittle]

Is overDue a Yes/No field in a table? If so it should be Yes, not "YES", in
the update query. -1 would work to update the field to Yes also.

 

[John Spencer MVP]

Well the posted SQL does not look correct to me. Is that an error on your
part in typing?

UPDATE Main
SET Main.OverDue= "YES"
WHERE Main.DueDate >= DateAdd("d",100,[InspectionDate])


UPDATE Main
SET Main.DaysTilNext = DateDiff("D",[InspectionDate], [DueDate])

Is there supposed to be a where clause on this query or are you trying to do
both updates in one query.

UPDATE Main
SET Main.OverDue= "YES"
, Main.DaysTilNext = DateDiff("d",[InspectionDate], [DueDate])
WHERE Main.DueDate >= DateAdd("d",100,[InspectionDate])

Since you can always calculate bothe values as they are needed, there is
really no reason to store them in a table except in the very rare case that
you have VERY large tables (several hundred thousand records) and need to
frequently filter records on the calculated values.

The big problem is that storing the data instead of calculating it when needed
means that whenever you change InspectionDate or DueDate in a record then you
have to make sure that Overdue and DaysTilNext get recalculated to show new
values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Belinda]

Jerry
Upper case vs. lower case did not resolve the problem, It should have been
an all or nothing update. But, I figured it out. Thank you for all your help.

 

[Belinda]

John
Correct, the code was not complete. I did not post the SELECT statement
portion. Not a problem anymore; I figured both issues out.
Thank you for all your help. I appreciate being able to ask for "help" and
direction here.