Assistance with Expression in Query for Date Greater Than or Less Than

[drex]

I am still new to Access queries and am having trouble filtering out less than and greater than time/date.



I need to display records from the Weekly Local Server Access List where the Start Time is less than the Access Start or the Start Time is greater than the Access End. Any ideas how I can accomplish this? Thanks!

Username	Group Name	Start Time	Access Start	Access End
user1	Administrators	04/06/2014 10:07:46 AM	04/14/2014 04:00:00 PM	04/18/2014 10:00:00 AM

[June7]

So you don't want any records that are between the Access Start and Access End? Are values in these 3 fields full date/time?

Try criteria under the Start Time field:

NOT BETWEEN [Access Start] AND [Access End]

[drex]

Sorry if I wasn't clear. I need to see all records in the weekly server access list and only records from grant access log which meet the following criteria..

Grant Access Log "access start" is greater than the weekly server access list "start time".

Username	Group Name	Start Time	Access Start	Access End
user1	Administrators	04/06/2014 10:07:46 AM	04/14/2014 04:00:00 PM	04/18/2014 10:00:00 AM

Grant Access Log "access end" is less than than the weekly server access list "start time".



Hopefully this spreadsheet displays what I need. The "show" result is what I want to see..

Username	Group Name	Start Time	Access Start	Access End	Result
user01	Administrators	04/06/2014 10:34	04/07/2014 10:00	04/11/2014 16:33	Show
user02	Administrators	04/06/2014 10:34	04/01/2014 10:34	04/06/2014 10:15	Show
user03	Administrators	04/06/2014 10:34	03/13/2014 16:50	04/11/2014 16:33	Do not show
user04	Administrators	04/06/2014 10:34	04/06/2014 10:34	04/11/2014 16:33	Do not show

[June7]

Try:

Criteria under [Access Start]

> [Start Time]

Criteria under [Access End]

< [Start Time]

Place each on separate OR rows in the grid.

How are these tables related? What are the primary and foreign key fields?

[drex]

That does not work because it works as an AND statement. I need an OR statement.

Try:

Criteria under [Access Start]

> [Start Time]

Criteria under [Access End]

< [Start Time]

Place each on separate OR rows in the grid.

How are these tables related? What are the primary and foreign key fields?

[June7]

See in earlier post: "Place each on separate OR rows in the grid."

[drex]

Thank you June7. It worked but now it caused another problem. When the query is run, I see Group Names which do not meet my defined criteria?

Username	Group Name	Start Time	Access Start	Access End
user1	Remote Desktop Users	04/06/2014 10:07:30 AM	03/15/2014 03:03:00 PM	03/16/2014 02:45:00 PM
user1	Remote Desktop Users	04/06/2014 10:07:30 AM	12/04/2013 01:15:00 PM	12/04/2013 03:00:00 PM
user2	Remote Desktop Users	04/06/2014 10:07:30 AM	02/10/2014 03:05:00 PM	02/11/2014 02:55:00 PM
user3	Remote Desktop Users	04/06/2014 10:07:30 AM	12/06/2013 04:34:00 PM	12/06/2013 08:00:00 PM
user5	Remote Desktop Users	04/06/2014 10:07:30 AM	03/26/2014 08:16:00 AM	03/26/2014 11:56:00 AM
user12	Remote Desktop Users	04/06/2014 10:07:30 AM	03/15/2014 12:15:00 PM	03/15/2014 03:00:00 PM
user6	Remote Desktop Users	04/06/2014 10:07:30 AM	02/01/2014 04:00:00 PM	02/02/2014 04:00:00 PM
user8	Remote Desktop Users	04/06/2014 10:07:30 AM	03/11/2014 09:45:00 AM	03/11/2014 02:00:00 PM

[ItsMe]

Considering

"...less than the Access Start or the Start Time is greater than the Access End..."

Try removing two of your criteria.


.

[drex]

That doesn't work. If I remove the criteria you specified, then the results show records within the time frame I don't want as well as others. It worked with both criteria boxes as I posted before. I am honestly surprised that this is so hard to figure out.

[ItsMe]

Post the SQL here and explain the business rules again, the reason for you building this query.

[drex]

Thanks ItsMe. Here is what I am trying to accomplish.. I have tables called "Weekly Local Server Access List" and "Grant Access Log". Both tables are linked to csv files which are updated by other applications. I only want to see entries in the Weekly Local Server Access List which do not overlap the same time as the grant access log.

I need a query to show only rows containing "username" and "group name" in the "Weekly Local Server Access List" table which do not equal matching rows containing "client" and "resource" in the "Grant Access Log" table where "access start" and "access end" are less than "start time" or "access start" and "access end" are greater than "start time".


My goal is to build an exception list so I can determine which users were placed in the group by another process outside of the Grant Access Log.

I hope this helps

SELECT [Weekly Local Server Access List].Username, [Weekly Local Server Access List].[Group Name], [Weekly Local Server Access List].[Server Name], [Weekly Local Server Access List].[Start Time], [Grant Access Log].[Access Start], [Grant Access Log].[Access End]
FROM [Weekly Local Server Access List] LEFT JOIN [Grant Access Log] ON [Weekly Local Server Access List].Username = [Grant Access Log].Client
WHERE ((([Weekly Local Server Access List].Username) Not Like "*sp$" And ([Weekly Local Server Access List].Username) Not Like "*gb$" And ([Weekly Local Server Access List].Username) Not Like "administrator*" And ([Weekly Local Server Access List].Username) Not Like "*pwrusers" And ([Weekly Local Server Access List].Username) Not Like "domain admin*" And ([Weekly Local Server Access List].Username) Not Like "*sp_adm" And ([Weekly Local Server Access List].Username) Not Like "*gb_adm" And ([Weekly Local Server Access List].Username) Not Like "svc*") AND (([Weekly Local Server Access List].[Group Name]) Like "administrator*" Or ([Weekly Local Server Access List].[Group Name]) Like "Power User*") AND (([Grant Access Log].[Access Start])<[start time]) AND (([Grant Access Log].[Access End])<[start time])) OR ((([Grant Access Log].[Access Start])>[start time]) AND (([Grant Access Log].[Access End])>[start time]));

[June7]

The date criteria "access start" and "access end" are less than "start time" or "access start" and "access end" are greater than "start time" is different from the original stated requirement. The following reflects that phrase.

Parens are critical when mixing AND and OR operators. Access throws in so many excess parens - I deleted all except what I think are essential. When you save the query, Access will throw them all back in.

WHERE [Weekly Local Server Access List].Username Not Like "*sp$" And [Weekly Local Server Access List].Username Not Like "*gb$" And [Weekly Local Server Access List].Username Not Like "administrator*" And [Weekly Local Server Access List].Username Not Like "*pwrusers" And [Weekly Local Server Access List].Username Not Like "domain admin*" And [Weekly Local Server Access List].Username Not Like "*sp_adm" And [Weekly Local Server Access List].Username Not Like "*gb_adm" And [Weekly Local Server Access List].Username Not Like "svc*" AND ([Weekly Local Server Access List].[Group Name] Like "administrator*" Or [Weekly Local Server Access List].[Group Name] Like "Power User*") AND (([Grant Access Log].[Access Start]<[start time] And [Grant Access Log].[Access End]<[start time]) OR ([Grant Access Log].[Access Start]>[start time] And [Grant Access Log].[Access End]>[start time]));