Comparing dates (1 Viewer)

[NotSoRandomOne]

The problem I'm having is that dates aren't comparing as I expect them to. In the 'After Update' event of a text box (formatted as a short date) named 'DateBox', I'm trying to update a combo box's 'RowSource' so the user only sees the appropriate information per the other information on the form.

The code:

   Dim theDate As Date
   ... (null check...)
   theDate = DateBox.Value
   ...
   TherapyOrderCombo.RowSource = "SELECT TherapyOrdersQuery.ID, TherapyOrdersQuery.TherapyOrder" _
               & " FROM TherapyOrdersQuery WHERE Therapy = " & TherapyTypeEnum.PhysicalTherapy _
               & " and TherapyOrdersQuery.CertEnd >= " & theDate

'CertEnd' is a short date in the original database. Unfortunately, the comparison statement doesn't update the row source correctly: all of the Therapy Orders are still listed in the combo box, even if their CertEnd falls before theDate. If I change it to a '<=' comparison, no therapy orders are listed.

After two hours, this has me baffled.

Thanks,
David

 

[boblarson]

Make sure your CertEnd field is not storing both date AND time.

 

[pbaldy]

Presuming it's a date/time value try:

& " and TherapyOrdersQuery.CertEnd >= #" & theDate & "#"

 

[NotSoRandomOne]

In the original database, the field is listed as 'Date/Time', with the 'Format' specified as 'Short Date'. The values show without a time ("i.e., 10/10/2009") Could the associated query be modifying this, and if so, how to modify it to compensate?

 

[boblarson]

In the original database, the field is listed as 'Date/Time', with the 'Format' specified as 'Short Date'. The values show without a time ("i.e., 10/10/2009") Could the associated query be modifying this, and if so, how to modify it to compensate?

No, I think Paul may be on to it.

 

[NotSoRandomOne]

Yes, that did it. A huge thanks! What does the '#' thingy tell Access to do, so I can understand it better?

David

 

[pbaldy]

It indicates to Access that the value is a date/time value. Otherwise it may do the math on 11/20/2009 and comes up with a really small number.

You surround date values with #, text values with quotes, numeric values with nothing.

 

[NotSoRandomOne]

Thank you both! Have a great day!