Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: need help on query for dates

    Hi there, Sorry if this rambles or doesn’t make sense but please bear with me.
    I have a little experience with access many years ago and now trying to get back into it. The problem i have is as follows.

    I’m trying to quiz a large excel spread sheet. It is basically made up of all our jobs manufactured and peoples clocking in and out on these jobs to manufacture them. I have managed to create a query that tells me all the items worked on for a given day. The trouble comes with the jobs worked on by the night shift. If i was to look at everything for the 1st of July it won’t return the items they started in the early hours of the 2nd but includes the items they started in the early hours of the 1st, which was on the previous days shift. (Hope that makes sense).

    The field’s i have to query on are:
    {REAL_CLOCKIN] & [REAL_CLOCKOUT]. The trouble is these are not actual date/time field’s but stored as a text field. There formatted as 2011-04-09 08:10:39.000

    I have managed to create a query that splits these columns into 2. 1 with a date and 1 with a time.
    Date In: Left([REAL_CLOCKIN],InStr(1,[REAL_CLOCKIN]," ")-1)
    Time: Right([REAL_CLOCKIN],Len([REAL_CLOCKIN])-InStr(1,[REAL_CLOCKIN]," "))

    Can anyone point me in a direction, apart from give up, that would return the info i need.

    Many thanks in advance

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You can easily convert the date strings as you have them to a standard date type:
    Public Function ConvertToDate(ByVal StringDate As Variant) As Variant
        If InStr(StringDate, ".") Then StringDate = Left(StringDate, InStr(StringDate, ".") - 1)
        If IsDate(StringDate) Then ConvertToDate = CDate(StringDate)
    End Function
    This function is declared Public and only uses Variant variables, so that you can use it from within a Query if you need so.

    From there, you can use the standard Date/Time functions (DateDiff in this case) to manipulate the data and perform computations on them.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts