Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    113

    Unanswered: [SQL]Problem with Date & Time

    I want to write a report that show posts between a certain startDate and startTime and endDate and endTime.

    I've currently written:
    Code:
    strSql = "[Date] Between #" & startDate & "# And [EndDate] & "#" _
    & "And [Time] Between #" & startTime & "# And [EndTime] & "#" _
    & "And [OrderName] Like " & "'" & Form_frmStart.cbo.OrderName.Value & "'"
    And I do not recive the correct answer.

    Would be great if anyone could help!
    -Julita

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...you do have all the SELECT/FROM/WHERE stuff hidden away somewhere, don't you?

    have a go with:
    strSql = "[Date] Between #" & startDate & "# And #" & [EndDate] & "# " _
    & "And [Time] Between #" & startTime & "# And #" & [EndTime] & "# " _

    i don't see any wildcard in the Like so maybe have a go with
    & "And [OrderName] = '" & Form_frmStart.cbo.OrderName.Value & "'"

    ...and i guess you are happy complicating your life by splitting the date and time (access has only datetime fields)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    First of, I recomend combining your date and time into one value as izyrider as already suggested.

    Personall,y I've run into the problems trying to use 'Between' and comparisons (e.g. <=, <, =, >, >=) with dates. The problem is that 08/03/2006 8:15:00 PM is a visual display of an internal =, number, which can be any one of a range of numbers, which can vary by -/+ half a second.

    The only way I've way I've manged to work around it is to use DateDiff(). Keep in mind that the Between statement is just two logical comparisons. The following are equivalent.
    Code:
    MyDateTime Between StartDateTime and EndDateTime
    
    (MyDateTime >= StartDateTime) AND (MyDateTime <= EndDateTime)
    So I use DateDiff as follows
    Code:
    ((DateDiff("s",StartDateTime,MyDateTime)>=0) AND 
    ((DateDiff("s",MyDateTime,EndDateTime))>=0)
    Here I am wanting to filter for all records where Date/Time are between (and include) my start and end date/time. Just changed the comparisons to suit your own needs.

    The reason I is "s" (i.e. seconds) in DateDiff is that I usually want to know of they are exactly the same.
    Code:
    'Find difference (Hours)
    DateDiff("h",#1/1/2006 10:00:00 PM#,#1/1/2006 10:00:01 PM#) = 0
    Your circumstance may be different.

    Also, if combining a Date and Time, I use DateSerial() + TimeSerial(). Keep in mind that 0.5 and 1.5 formatted as time are both 12:00 PM, since only the everything to the left decimal is the time portion and to the right of the decimal is the date. I've had occasions where a field is formatted as 'Long Time' (e.g 8:45:00 PM), 'Medium Time' (e.g 8:45 PM), or 'Short time' (e.g 20:45) but the value was a Time and Date (i.e. >=1).
    Code:
    DateSerial(Year(DateValue), Month(DateValue), Day(DateValue)) + 
    TimeSerial(Hour(TimeValue), Minute(TimeValue), Second(TimeValue))
    That can be a lot to type but you can always create a custom function that accepts two date fields and returns a FULL Date + Time using one field for the date and the other for the Time.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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