tonelm54
asked on
Days not working
Ive written a query to find out if users passwords will expire soon:-
Which works great, and gives me all the users and the date of their last password, and how many days its old. However I want to only view the passwords which are older than 90 days, so put a "WHERE DaysSince >90":-
But keeps telling me I have a syntax error. I changed it to a 'Having':-
But still says there is a syntax error in my query.
Can anyone see where Im going wrong with this please?
SELECT [_Users].User, [_Users].[Currently Employed], Max([_Users_Passwords].Changed) AS [Last Changed], DateDiff("d",[Last Changed],Now()) AS DaysSince
FROM _Users INNER JOIN _Users_Passwords ON [_Users].User = [_Users_Passwords].User
GROUP BY [_Users].User, [_Users].[Currently Employed]
HAVING ((([_Users].[Currently Employed])=True))
ORDER BY [_Users].User;
Which works great, and gives me all the users and the date of their last password, and how many days its old. However I want to only view the passwords which are older than 90 days, so put a "WHERE DaysSince >90":-
SELECT [_Users].User, [_Users].[Currently Employed], Max([_Users_Passwords].Changed) AS [Last Changed], DateDiff("d",[Last Changed],Now()) AS DaysSince
FROM _Users INNER JOIN _Users_Passwords ON [_Users].User = [_Users_Passwords].User
GROUP BY [_Users].User, [_Users].[Currently Employed]
HAVING ((([_Users].[Currently Employed])=True))
WHERE DaysSince >90
ORDER BY [_Users].User;
But keeps telling me I have a syntax error. I changed it to a 'Having':-
SELECT [_Users].User, [_Users].[Currently Employed], Max([_Users_Passwords].Changed) AS [Last Changed], DateDiff("d",[Last Changed],Now()) AS DaysSince
FROM _Users INNER JOIN _Users_Passwords ON [_Users].User = [_Users_Passwords].User
GROUP BY [_Users].User, [_Users].[Currently Employed]
HAVING ((([_Users].[Currently Employed])=True) AND ((DateDiff("d",[Last Changed],Now()))>90))
ORDER BY [_Users].User;
But still says there is a syntax error in my query.
Can anyone see where Im going wrong with this please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.