Sometimes you need to present different data to different groups of users. Let's say you run a classified ads board and wish to display the data added over the past 7 days to regular users and all of the ads to the admin.
Add the following code to the After table initialized event.
1. For MS SQL Server database:
if Security_getUserName()<>"admin" then
query.addWhere "DATEDIFF(day,DateColumn,GETDATE())<=7"
end if
2. For MySQL database:
if Security_getUserName()<>"admin" then
query.addWhere "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DateColumn"
end if
3. For MS Access database:
if Security_getUserName()<>"admin" then
query.addWhere "DateDiff('d',DateColumn,Now())<=7"
end if
4. If you use Dynamic Permissions, the code should be different (MySQL example).
Note: If the user doesn't belong to the admin group, show only the past 7 days worth of data.
if not Security_isAdmin() then
query.addWhere "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DateColumn"
end if
See also:
•Method: addWhere
•SQL variables in SQL and Lookup wizards