Problems comparing dates and times(i.e < or >)

[xtreme1]

&nbsp;&nbsp;&nbsp;&nbsp;Hey guys, having some problems comparing dates.&nbsp;&nbsp;When using the &quot;&gt;&quot; or &quot;&lt;&quot; symbols, the dates are usually compared by what seems like the first 2 digits?&nbsp;&nbsp;In some cases the comparison seems to actually follow no logical pattern what-so-ever.&nbsp;&nbsp;I have figured out how I can sort SQL queries by time, which allows organized &quot;per stop&quot; reports in sequential order from morning to night.&nbsp;&nbsp;Before you can use ASC in your SQL query though, it seems you need to use:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Format([YourTimeFrame], &quot;hh:nn&quot<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Is anyone aware of a better method?&nbsp;&nbsp;It seems that even with this formatting in my code, comparisons like the following still do not work.<br><br>If Format([TimeShouldBeThere], &quot;hh:nn&quot &gt; Format(Time(), &quot;hh:nn&quot Then<br>your_late = True<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I appreciate any experiences with this problem shared with me, I'm sure it will be helpful.&nbsp;&nbsp;Thanks!<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br> <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>

 

[DougP]

Is this a field in your database or just a key in.<br>In either case try setting the field to a Date/Time field and not text.<br><br>If you have a variable in your code do this before you use it.<br>Dim MyDate as Date<br><br>Also put a pound symbol on each side of a date<br><br>like #12/13/99#<br>this will force it to be date <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[xtreme1]

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;This is data pulled into a recordset from a field in my database.&nbsp;&nbsp;The actual data itself is a time, not a date, so I'm unsure of whether or not I can use the &quot;#&quot; symbol.&nbsp;&nbsp;I'll give that a shot though, thanks. <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>

 

[johnmichael]

I recently encountered a problem using IIf statements.&nbsp;&nbsp;I was running queries based on several subqueries and tables and did not realize that the variable I was evaluating was on some rare occasions initialized to a null value.&nbsp;&nbsp;For instance, if

 

[RoyVidar]

I've had the same problem, and started using DateDiff(), which worked well in my case. <br><br>If DateDiff(&quot;n&quot;,Time(), [TimeShouldBeThere]) &gt; 0 Then<br>&nbsp;&nbsp;your_late = True<br><br>Roy-Vidar

 

[xtreme1]

Roy, I found that the same solution was useful.&nbsp;&nbsp;Also, I converted to military time and did some Left(), Right() evaluations.&nbsp;&nbsp;I'm sure most people used the military method before, but for some reason I'm stubborn and like to do things differently.&nbsp;&nbsp;Anyway, DateDiff or giving up and converting to military time both work, thanks for the tips guys. =] <p>xtreme1<br><a href=mailto:rbio@excite.com>rbio@excite.com</a><br><a href= > </a><br>

 

[JimHorton]

xtreme1,<br>Dougp was on the right track...when you use Format() you are returning a string, no matter what data type you started with.&nbsp;&nbsp;If you did: CVdate(Format(&quot;somedate&quot;,&quot;mm/dd/yyyy&quot), this would solve it also.&nbsp;&nbsp;Bottom line--no need to use DateDiff (for comparisons), or convert to military time--the comparison will always work correctly if you are using Date data type, because you are really comparing a Floating point number.&nbsp;&nbsp;As soon as you start comparing strings, things get tricky.<br>--Jim