Full Version: Dates Beyond three months away
UtterAccess Forums > MicrosoftŪ Access > Access Date + Time
AndyNonymous
I have a table with a column named Scheduled Verification Finish, this column contains both dates and text. I want a query that count how many dates in this column fall outside of the next three months. I have
CODE
SELECT Count([Q&R Task List].[Line No]) AS [CountOfLine No], [Q&R Task List].Current, Format([scheduled verification finish],"mm/yy") AS [year], [Q&R Task List].[Scheduled Verification Finish], Format(DateAdd("m",2,Date()),"mm/yy") AS Expr1
FROM [Q&R Task List]
GROUP BY [Q&R Task List].Current, Format([scheduled verification finish],"mm/yy"), [Q&R Task List].[Scheduled Verification Finish], Format(DateAdd("m",2,Date()),"mm/yy")
HAVING ((([Q&R Task List].Current)=True) AND ((Format([scheduled verification finish],"mm/yy"))>Format(DateAdd("m",2,Date()),"mm/yy")) AND (([Q&R Task List].[Scheduled Verification Finish]) Like "#*#"));

^^^ Is the sql to my query so far. However it still picks up dates that fall in the next three months. Could anyone shed any light on how I might coax it into working? BTW the 'Line No' is my PK and 'Current' is checked in order to only select the current records.
Peter46
'this column contains both dates and text'
So this must be a text datatype and you cannot use date processing on it.
You have to have some way of isolatiing the date part of the field. You will have to post some sample field values to get proper help.
jsitraining
Not possible. It may contain text that is representative of a date, but it does not contain dates (subtle but VERY important distinction) Now when you say this, do you mean that each row will contain text AND "date" or do you mean that some rows will contain text and some rows will contain "Dates". Or do you mean a mixture of the two?
In any way, the first thing that you need to do is sort out your data structure before you go any further.
When poting sql, it is really helpful if you put different clauses on different lines, even to the ectent of different column (fields) on different lines. It makes it MUCH easier to read:
CODE

SELECT Count([Q&R Task List].[Line No]) AS [CountOfLine No]
     , [Q&R Task List].Current
     , Format([scheduled verification finish],"mm/yy") AS [year]
     , [Q&R Task List].[Scheduled Verification Finish], Format(DateAdd("m",2,Date()),"mm/yy") AS Expr1
FROM [Q&R Task List]
GROUP BY [Q&R Task List].Current
               , Format([scheduled verification finish],"mm/yy")
               , [Q&R Task List].[Scheduled Verification Finish]
               , Format(DateAdd("m",2,Date()),"mm/yy")
HAVING ((([Q&R Task List].Current)=True)
AND ((Format([scheduled verification finish],"mm/yy"))>Format(DateAdd("m",2,Date()),"mm/yy"))
AND (([Q&R Task List].[Scheduled Verification Finish]) Like "#*#"));

In order to restrict rows returned to only show rows After the 3month barrier put in a where clause:
DateDiff("m", Date(),[scheduled verification finish])>3
1 final point, do not have table, field, object names with spaces in them. If you must have spaces ..... don't. wink.gif A common Convention is to use underscores _.
HTH
Jim
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.