Date conversion comparison not working

[kit_sune]

Hey everyone, thanks for all your help in the past, I really appreciate it. It's helped me to grow and get better at understanding how DB's operate. Unfortunately, I recently (temporarily)took over a position that uses an Access database that does not work properly, and I'm stumpped on how to fix it.

The query is supposed to pull all data where the "Date Overdue" field is less than today.
"Date Overdue" is a calculated value that pulls from the field "Date Input", which is in a text format (DDMMMYY) Such as 03NOV13. It is 8 days after the date input.

It prints out like this: "Monday, November 11, 2013" which is 8 days after the 3rd.

"Date Overdue" is set to this value:

DATE OVERDUE: DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8

"Date Overdue" has the criteria "<DateValue(CDate(Now()))"

I'm not going to go into all the different steps I've taken to try and get this to work because I've toyed with it a lot..



The output that I always seem to get is a mixture of all records that are available, before and after today's date, I just wanted those that are less than today.

I suspect that the date values that are shown in the query aren't true dates because when I click on the filter button it gives me this error:
"Syntax error (missing operator) in query expression 'DATE OVERDUE' "

Any ideas?

Thanks again,

~Kit

NOTE: I'd like to add that this is just a regular Select query.

SELECT DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2)) AS [PRODUCT END PERIOD], DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8 AS [DATE OVERDUE], [QBR ON EQUIP].DATEINPUT, [ALL ERRORS].[ERROR STATUS], [WORKCENTER CODES].[ORGID NOMEN], [QBR ON EQUIP].PWC
FROM [WORKCENTER CODES] INNER JOIN ([QBR ON EQUIP] INNER JOIN [ALL ERRORS] ON [QBR ON EQUIP].EVTWCEDDR = [ALL ERRORS].EVTWCEDDR) ON [WORKCENTER CODES].WORKCENTER = [QBR ON EQUIP].PWC
WHERE (((DateValue(Left([DATEINPUT],2) & "/" & Mid([DATEINPUT],3,3) & "/" & Right([DATEINPUT],2))+8)<DateValue(CDate(Now()))) AND (([ALL ERRORS].[ERROR STATUS])="ERROR NOT CORRECTED")) OR ((([ALL ERRORS].[ERROR STATUS])="NOT REVIEWED"));

 

[namliam]

Not a solution to start with with ... why???
<DateValue(CDate(Now()))

Why not simply do <Date()

Basicaly your formula should be sound, unless "Dates" are in the format of DMMMYY in which case things may screw up. Given your sample of 03NOV13 you shouldnt have a problem

Things I can see a problem with... 13... you are missing the century, which means access may screw up and make it 1913 for some reason....
Try doing "20" & Right(..,2) for the year.

Capsule the date conversion in some Iif, to atleast ensure the string is the expected 7 characters and that perhaps it starts and ends with 2 numbers and perhaps look at the 3 chars to make sure it is an actual Month i.e. someone may have made a typo Nob instead of Nov which will obviously screw up the CDate

Lastly the dreaded ()

WHERE 
(
 (
  (DateValue(Left([DATEINPUT],2) & "/" & 
             Mid([DATEINPUT],3,3) & "/" & 
             Right([DATEINPUT],2)
            )+8
  )<DateValue(CDate(Now()))
 ) 
 AND 
 (
  ([ALL ERRORS].[ERROR STATUS])="ERROR NOT CORRECTED"
 )
) 
OR 
(
 (
  ([ALL ERRORS].[ERROR STATUS])="NOT REVIEWED"
 )
);

Is it your intention to have NOT REVIEWED seperate from your date restriction? It may be the reason why you are seeing unexpected dates.