UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> #error Results On Matching Dates In Two Tables, Access 2016    
 
   
jcartwright0000
post Feb 14 2018, 04:50 PM
Post#1



Posts: 2
Joined: 14-February 18



I know I have this all wrong, but I just can't seem to get anywhere. I have two queries that pull from a table in Access that holds timestamps of when clients sign in and out. One query is the sign in entries and the other is the sign out entries. If the client is signed out and then signed back in, I'm wanting to calculate that time difference. If the client is only signed out but never signed back in, I'm wanting to calculate the time difference between the end time of their schedule (in another query) and the sign out time. The first part works just fine, but it's the clients that don't sign back in that are a problem since there's obviously no record of them signing in on that particular date. So Access freaks out and returns the #Error. Here's my SQL below. Where should I go from here?

SQL
SELECT [Sign Out Entries].ClientID, [Sign Out Entries].ClockingDate, IIf([Sign In Time] Is Null,DateDiff("n",[EndTime],[Sign Out Time]),DateDiff("n",[Sign In Time],[Sign Out Time])) AS Difference
FROM ([Sign In Entries] RIGHT JOIN [Sign Out Entries] ON ([Sign In Entries].[Clocking Date] = [Sign Out Entries].ClockingDate) AND ([Sign In Entries].ClientID = [Sign Out Entries].ClientID)) INNER JOIN FinalPeriodEndTimebyClient ON [Sign Out Entries].ClientID = FinalPeriodEndTimebyClient.ClientID;
Go to the top of the page
 
jcartwright0000
post Feb 14 2018, 05:02 PM
Post#2



Posts: 2
Joined: 14-February 18



I think I figured it out. Instead of checking if [Sign In Time] Is Null, I changed it to [Client ID]. Here's the correct SQL!

SQL
SELECT [Sign Out Entries].ClientID, [Sign Out Entries].ClockingDate, IIf([Sign In Entries].[ClientID] Is Null,DateDiff("n",[EndTime],[Sign Out Time]),DateDiff("n",[Sign In Time],[Sign Out Time])) AS Difference
FROM ([Sign In Entries] RIGHT JOIN [Sign Out Entries] ON ([Sign In Entries].[Clocking Date] = [Sign Out Entries].ClockingDate) AND ([Sign In Entries].ClientID = [Sign Out Entries].ClientID)) INNER JOIN FinalPeriodEndTimebyClient ON [Sign Out Entries].ClientID = FinalPeriodEndTimebyClient.ClientID;
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st August 2018 - 06:11 PM