Date Comparisons

[Keven Denen]

I have a CRM database that I'm having some trouble making work for a
customer I have in the UK.

I've got a reminder form that pops up if the have an activity that is
overdue. I use a SQL statment to pull the information I need to match
only those records that are overdue (based on ReminderTime which is a
Date/Time field) for whichever sales rep is logged in. There is a
record set up right now with a date of August 7, 2009 (UK format
8/7/09). Today's date is July 31, 2009 (UK format 31/7/09). The query
finds this record, even though it isn't really past due.

sSQL = "SELECT [Contact Activities].ActivitiesID, [Contact
Activities].CompanyID, [Contact Activities].Contact,
Company.CompanyName AS Company, [Contact Activities].[Contact Date] AS
[Date], [Contact Activities].callback,[Contact Activities].apptStart
AS [Time], [Contact Activities].activity AS Activity, [Contact
Activities].Activitydescription AS Description, [Contact
Activities].nextaction,[ReminderTime] AS expr1 " _
& "FROM Company INNER JOIN [Contact Activities] ON Company.CompanyID =
[Contact Activities].CompanyID " _
& "WHERE (([ReminderTime]<Now()) AND (([Contact
Activities].contactedby)=[Forms]![Login Dialog]![Salesrep]));"

I use that SQL statement to populate a list box.

lstReminder.RowSource = sSQL

It works fine for a US customer, but on a machine set for UK format,
the comparison it does against the ReminderTime is wonky. I set up
some code to output the info that the query is receiving.

Dim rs as DAO.Recordset
Set rs = lstReminder.Recordset
rs.MoveFirst
Do While Not rs.EOF
If Not IsNull(rs!Expr1) Then
If rs!Expr1 < Now() Then
Debug.Print rs!Expr1 & " is less than " & Now()
End If
rs.MoveNext
End If
Loop

The Debug.Print outputs "08/07/2009 13:45:00 is less than 31/07/2009
23:53:09"

Anyone know of a way I can get Access to to compare those dates
properly? I'm sure it's something easy I'm missing, but I can't see
it.

Thanks.

Keven Denen

 

[John Spencer]

Well July 8, 2009 is less than July 31, 2009. You are displaying the
dates in the UK format of day month year. If you want to confirm that
change your debug print to

Debug.print Format(rs!expr1,"dd mmm yyyy") & "is less than " &
Format(Now(),"dd mmm yyyy")

Or if you want use the format yyyy mm dd

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Keven Denen]

Well July 8, 2009 is less than July 31, 2009.  You are displaying the
dates in the UK format of day month year.  If you want to confirm that
change your debug print to

   Debug.print Format(rs!expr1,"dd mmm yyyy") & "is less than " &
Format(Now(),"dd mmm yyyy")

Or if you want use the format yyyy mm dd

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

I have a CRM database that I'm having some trouble making work for a
customer I have in the UK.

I've got a reminder form that pops up if the have an activity that is
overdue. I use a SQL statment to pull the information I need to match
only those records that are overdue (based on ReminderTime which is a
Date/Time field) for whichever sales rep is logged in. There is a
record set up right now with a date of August 7, 2009 (UK format
8/7/09). Today's date is July 31, 2009 (UK format 31/7/09). The query
finds this record, even though it isn't really past due.

sSQL = "SELECT [Contact Activities].ActivitiesID, [Contact
Activities].CompanyID, [Contact Activities].Contact,
Company.CompanyName AS Company, [Contact Activities].[Contact Date] AS
[Date], [Contact Activities].callback,[Contact Activities].apptStart
AS [Time], [Contact Activities].activity AS Activity, [Contact
Activities].Activitydescription AS Description, [Contact
Activities].nextaction,[ReminderTime] AS expr1 " _
& "FROM Company INNER JOIN [Contact Activities] ON Company.CompanyID =
[Contact Activities].CompanyID " _
& "WHERE (([ReminderTime]<Now()) AND (([Contact
Activities].contactedby)=[Forms]![Login Dialog]![Salesrep]));"

I use that SQL statement to populate a list box.

lstReminder.RowSource = sSQL

It works fine for a US customer, but on a machine set for UK format,
the comparison it does against the ReminderTime is wonky. I set up
some code to output the info that the query is receiving.

    Dim rs as DAO.Recordset
    Set rs = lstReminder.Recordset
    rs.MoveFirst
    Do While Not rs.EOF
        If Not IsNull(rs!Expr1) Then
                If rs!Expr1 < Now() Then
                    Debug.Print rs!Expr1 & " is less than " & Now()
                End If
            rs.MoveNext
        End If
    Loop

The Debug.Print outputs "08/07/2009 13:45:00 is less than 31/07/2009
23:53:09"

Anyone know of a way I can get Access to to compare those dates
properly? I'm sure it's something easy I'm missing, but I can't see
it.

Thanks.

Oops, sorry the date it outputs is 07/08/2009. August 8th.

Keven Denen

 

[Keven Denen]

Well July 8, 2009 is less than July 31, 2009.  You are displaying the
dates in the UK format of day month year.  If you want to confirm that
change your debug print to

   Debug.print Format(rs!expr1,"dd mmm yyyy") & "is less than " &
Format(Now(),"dd mmm yyyy")

Or if you want use the format yyyy mm dd

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

I have a CRM database that I'm having some trouble making work for a
customer I have in the UK.

I've got a reminder form that pops up if the have an activity that is
overdue. I use a SQL statment to pull the information I need to match
only those records that are overdue (based on ReminderTime which is a
Date/Time field) for whichever sales rep is logged in. There is a
record set up right now with a date of August 7, 2009 (UK format
8/7/09). Today's date is July 31, 2009 (UK format 31/7/09). The query
finds this record, even though it isn't really past due.

sSQL = "SELECT [Contact Activities].ActivitiesID, [Contact
Activities].CompanyID, [Contact Activities].Contact,
Company.CompanyName AS Company, [Contact Activities].[Contact Date] AS
[Date], [Contact Activities].callback,[Contact Activities].apptStart
AS [Time], [Contact Activities].activity AS Activity, [Contact
Activities].Activitydescription AS Description, [Contact
Activities].nextaction,[ReminderTime] AS expr1 " _
& "FROM Company INNER JOIN [Contact Activities] ON Company.CompanyID =
[Contact Activities].CompanyID " _
& "WHERE (([ReminderTime]<Now()) AND (([Contact
Activities].contactedby)=[Forms]![Login Dialog]![Salesrep]));"

I use that SQL statement to populate a list box.

lstReminder.RowSource = sSQL

It works fine for a US customer, but on a machine set for UK format,
the comparison it does against the ReminderTime is wonky. I set up
some code to output the info that the query is receiving.

    Dim rs as DAO.Recordset
    Set rs = lstReminder.Recordset
    rs.MoveFirst
    Do While Not rs.EOF
        If Not IsNull(rs!Expr1) Then
                If rs!Expr1 < Now() Then
                    Debug.Print rs!Expr1 & " is less than " & Now()
                End If
            rs.MoveNext
        End If
    Loop

The Debug.Print outputs "08/07/2009 13:45:00 is less than 31/07/2009
23:53:09"

Anyone know of a way I can get Access to to compare those dates
properly? I'm sure it's something easy I'm missing, but I can't see
it.

Keven Denen

Hmm...indeed. I wonder why even though I input it as August 7th, 2009,
the code pulls it out as 08/07/09.

Keven Denen

 

[John W. Vinson]

Hmm...indeed. I wonder why even though I input it as August 7th, 2009,
the code pulls it out as 08/07/09.

Don't confuse data STORAGE with data PRESENTATION. August 7 is actually stored
as a count of days since midnight, December 30, 1899 - 40032.000000000.

Microsoft's programmers (being Americans, mostly) chose to require that date
*literals* in an expression must be in mm/dd/yyyy format, or an unambiguous
format such as 7-Aug-2009 or 2009-08-07. The date will be displayed in any
format you specify; if you don't specify, then it will use your computer's
Start... Control Panel... Regional and Language setting.