Thread: [SQL]Problem with Date & Time
08-03-06, 17:22 #1Registered User
- Join Date
- Nov 2005
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:
strSql = "[Date] Between #" & startDate & "# And [EndDate] & "#" _ & "And [Time] Between #" & startTime & "# And [EndTime] & "#" _ & "And [OrderName] Like " & "'" & Form_frmStart.cbo.OrderName.Value & "'"
Would be great if anyone could help!
08-03-06, 17:42 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
...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)
izycurrently using SS 2008R2
08-03-06, 22:50 #3Registered User
- Join Date
- Apr 2003
- Alabama, USA
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.
MyDateTime Between StartDateTime and EndDateTime (MyDateTime >= StartDateTime) AND (MyDateTime <= EndDateTime)
((DateDiff("s",StartDateTime,MyDateTime)>=0) AND ((DateDiff("s",MyDateTime,EndDateTime))>=0)
The reason I is "s" (i.e. seconds) in DateDiff is that I usually want to know of they are exactly the same.
'Find difference (Hours) DateDiff("h",#1/1/2006 10:00:00 PM#,#1/1/2006 10:00:01 PM#) = 0
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).
DateSerial(Year(DateValue), Month(DateValue), Day(DateValue)) + TimeSerial(Hour(TimeValue), Minute(TimeValue), Second(TimeValue))http://AccessDB.Info
You live and learn. At any rate, you live. - Douglas Adams