Date compare issue 2006 & 2007

[bartw72]

I don't know if anyone else has had this problem when changing from one year to the next, but I haven't found any postings regarding date compare issues. I have a two-fold problem:

1. I have a combo box that lists available dates from a table in Descending order so the most recent date is on top. This worked in the last half of 2006, but when the year changed it is now listing the 2007 dates at the bottom of the list. The field is a Short Date in mm/dd/yyyy format. How do I list them in descending order (01/02/2007, 01/01/2007, 12/31/2006, 12/30/2006...)?

2. The user can also type a date in this field to enter a new record that isn't in the table, but I have After_Update code that also compares the date entered with today's date and gives a message if they enter a future date. This also worked in 2006, but now it is populating the future date message if they enter a date in the past. Here is part of the code:

'*****************************************

Private Sub cmbDate_AfterUpdate()

Dim strDate As String
Dim intResponse As Integer

strToday = Date

If IsNull(cmbDate) Or Len(cmbDate.Value) < 1 Then
MsgBox "Please select or enter a valid date."
cmbDate.SetFocus
Exit Sub
Else
strDate = cmbDate.Value
End If
If strDate > strToday Then
intResponse = MsgBox("You entered a future date. Do you want to continue with this date?", vbYesNo)
If intResponse = vbNo Then
cmbDate.Value = Date
Exit Sub
End If
End If

'**********************************************

I would appreciate any advise.

 

[FoFa]

Because you are converting your dates to strings.
Either leave them as date datatypes for comparison/sorting purposes,
or convert them to YYYYMMDD format

 

[KeithG]

Is the Date field in your table of a text or date/time data type. It sounds to me that it is a text data type.

 

[boblarson]

1. I have a combo box that lists available dates from a table in Descending order so the most recent date is on top. This worked in the last half of 2006, but when the year changed it is now listing the 2007 dates at the bottom of the list. The field is a Short Date in mm/dd/yyyy format. How do I list them in descending order (01/02/2007, 01/01/2007, 12/31/2006, 12/30/2006...)?

For your rowsource for the combo box, you need to set it to be a query and not the table. The query will be based on the table, but then you can set the sort to be whatever you want (either Ascending or Descending).

For your second part, you have set the dates to strings and so comparing them 1/ comes before 12/ and so in descending order would show as the last items. Compare them as dates and not strings.

 

[bartw72]

Date Compare Issue 2006 & 2007

I changed the format of the date fields to DATE in the table and in the code and then I remembered why I stored them as text: I am doing a compare in an insert statement and I get a data type missmatch message when using a date format:

****************
dbAddTracking.Execute ("INSERT INTO tblTracking (dteDate, strUserName) SELECT tblTracking.dteDate, tblTracking.strUserName FROM tblTracking WHERE dteDate = '" & dteRecordDate & "' AND strUserName = '" & fOSUserName() & "'")
**************

Both the dteDate dteRecordDate values are Dim'd as "Date". When I store them as "Strings" the [WHERE dteDate = '" & dteRecordDate & "'] doesn't error for data type missmatch. Any thoughts?

 

[boblarson]

Use the convert code CDate in your rowsource query to convert them to a date. So the query would essentially be like:

SELECT CDate([YourDateFieldHere]) AS MyDate
FROM YourTableNameHere;

 

[RoyVidar]

When working with dates, and stuffing them into strings which you feed the Jet engine, the dates need unambiguous format

...dteDate = #" & Format$(dteRecordDate, "yyyy-mm-dd") & "# AND...

check out for instance
http://allenbrowne.com/ser-36.html, http://www.mvps.org/access/datetime/date0005.htm

I prefer the ISO 8601 format http://en.wikipedia.org/wiki/ISO_8601