UtterAccess Forums > Dates Beyond three months away

Full Version: Dates Beyond three months away

UtterAccess Forums > Microsoft® Access > Access Date + Time

AndyNonymous

Nov 21 2005, 07:05 AM

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

Nov 21 2005, 07:15 AM

'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

Nov 21 2005, 07:18 AM

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.

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.