Link to home
Start Free TrialLog in
Avatar of tonelm54
tonelm54

asked on

Days not working

Ive written a query to find out if users passwords will expire soon:-
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;

Open in new window


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;

Open in new window


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;

Open in new window


But still says there is a syntax error in my query.

Can anyone see where Im going wrong with this please?
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial