Search results

I apologize, I'm definitely a new user - I thought I was using TimeValue correctly and adding the :00 in my original function: Val(Left(Format(TimeValue(Format(TimeValue(Left([starttime],2) & ":00"),"Short Time"))-1-TimeValue(Format(TimeValue(Left([Endtime],2) & ":00"),"Short Time")),"Short...

The EndTime field varies depending on the rest of the query, for example in the one I am trying to pull it gives 1600 if I just have the "EndTime" value

Here is the SQL from the query - I'm very new to access and usually build queries in the design view - SELECT IMCDate([AssignDate]) AS [DATE], OfficerAttendance.AssignCode, OfficerAttendance.DutyCode, PersonnelFileInfo.LastName, PersonnelFileInfo.FirstName, OfficerAttendance.StartTime...

Yes sorry, I should have specified, I tried it in the "EndTime" field and get #Error" in the "Expr1" column.

I am having some trouble using TimeValue - I am putting the following in the "Field" : Expr1: TimeValue([StartTime])

I would love to, but it is external ODBC data being linked to Access, is there a way to convert the field to date/time in a query and then use DateDif?

I have a query that calculates the elapsed time between [StartTime] and [EndTime], both stored as text in the 24 hour/military time format. This is what I am currently using but it only returns a whole number, such as 1 or 2, but ignores the minutes, so if it should be 1:30 (1.5) is just shows...

Thank you for the quick reply - Am I adding that into the criteria for the same Field and Table, if so it does not work - I have attached the SQL so you have an idea of the entire query: SELECT IMCDate([AssignDate]) AS [Date], PersonnelFileInfo.LastName, PersonnelFileInfo.Rank...

I'm not sure it will help anyone else, but by using the module I was able to get a DateSerial to work: Expr1: IMCDate([AssignDate]) And then in criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)

Unfortunately the data is stored from a software program - when I open the tables I only see it stored as yyyymmdd, the company that provides the software did provide a "module" that converts the date fields to the standard mm/dd/yyyy format - so maybe there is a way to use this in the query...

I am looking to setup a query that will run on the previous month's data, the db has dates stored in the yyyymmdd format, which is what I believe is causing issues with any criteria I have tried to use. It will be pulling from a table called "Attendance" and a Field called "AssignDate". Thank you!