Microsoft Access 2010 Working with Dates Too Complex error
I have a tbltimesheet. It has starttime and stoptime my query calculates the difference between starttime and stoptime. I am trying to limit the data to dates between two certain start dates 10/1/2013 and 10/30/2013 for example. I change the starttime to a datevalue([starttime]) There may be nulls in the query so I filtered them out with not null. Everytime I try to limit my results I get a too complex error message. It works fine for all of the records. Thanks for the help.
Can you post the syntax of the query? It would help in our efforts to identify why Access thinks the query is too complex. Although I regularly use the DateValue() function, I prefer to work with raw dates whenever possible. What I generally do to query date/time values is use a syntax that looks like:
WHERE [StartTime] >= #10/1/2013# AND [StartTime] < #1/11/2013#
It is imperative that when filtering fields that contain both date and time values, that you don't inadvertently filter out some records for the last day in the period by using a syntax that lookslike:
WHERE [StartTime] >= #10/1/2013# AND [StartTime] <= #10/30/2013#
This would exclude any records where the [StartTime] looked like:
10/30/2013 00:00:01 => 10/30/2013 23:59:59
WHERE [StartTime] >= #10/1/2013# AND [StartTime] < #1/11/2013#
It is imperative that when filtering fields that contain both date and time values, that you don't inadvertently filter out some records for the last day in the period by using a syntax that lookslike:
WHERE [StartTime] >= #10/1/2013# AND [StartTime] <= #10/30/2013#
This would exclude any records where the [StartTime] looked like:
10/30/2013 00:00:01 => 10/30/2013 23:59:59
You could use the DateValue() function to extract only the data part of a datetime field.
Where DateValue(SomeDate) Between Forms!yourform!startdate AND Forms!yourform!enddate
Of course, if you posted the code for the query, we wouldn't have to guess at what the problem might be.
Where DateValue(SomeDate) Between Forms!yourform!startdate AND Forms!yourform!enddate
Of course, if you posted the code for the query, we wouldn't have to guess at what the problem might be.
Just for clarification there are 31 days in October the suggested sql should be
WHERE [StartTime] >= #10/1/2013# AND [StartTime] < #10/31/2013#
WHERE [StartTime] >= #10/1/2013# AND [StartTime] < #10/31/2013#
Emil,
The OP used 10/30/13, so that is what I used.
;-)
The OP used 10/30/13, so that is what I used.
;-)
ASKER
Here is the file. I will the final output to have a total number of hours and minutes by employee like for example starttime 12/13/2013 1:00 stop time 12/13/2013 2:30 would be 1:30. I use a function modtominute for this
MinToTime = MyMinute \ 60 & ":" & Format((Abs(MyMinute Mod 60)), "00")
The user would put their start time and stop time and click the button on the form. In the end I will have a summary form from the query. May I ask at this time how to total up the hours for each employee? When I did sum it looked like the total was high when I was working with all of the records. Thanks for the help
timelog.accdb
MinToTime = MyMinute \ 60 & ":" & Format((Abs(MyMinute Mod 60)), "00")
The user would put their start time and stop time and click the button on the form. In the end I will have a summary form from the query. May I ask at this time how to total up the hours for each employee? When I did sum it looked like the total was high when I was working with all of the records. Thanks for the help
timelog.accdb
ASKER
I should have said stop and start date not time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the replies. I used the second query capricorn1 gave me and it work perfectly. I don't understand why my way was getting the error message but I have the solution.
have you done a compact and repair?