CODEWITHSUNDEEP
sqlms-access
PeanutsMonkey
PeanutsMonkey

Reputation: 7095

Issues when searching between dates Microsoft Access

The query below does not return any values.

SELECT o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, 
       o.Name, type.Property, type.Value
FROM ((t_object as o
       INNER JOIN t_objectproperties as location
         ON o.Object_ID = location.Object_ID)
       INNER JOIN t_objectproperties as type 
         ON o.Object_ID = type.Object_ID)
WHERE location.Property = 'Location' 
  AND location.Value = 'ND'
  AND type.Property = 'FDate'
  AND CDate(type.Value) >= CDate('21/02/2014') 
  AND CDate(type.Value) <= CDate('24/02/2014')

However if i do the following it returns a set of results that don't fall between the required ranges

SELECT o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, 
     o.Name, type.Property, type.Value
  FROM ((t_object as o
         INNER JOIN t_objectproperties as location
           ON o.Object_ID = location.Object_ID)
         INNER JOIN t_objectproperties as type 
           ON o.Object_ID = type.Object_ID)
  WHERE location.Property = 'Location' 
    AND location.Value = 'ND'
    AND type.Property = 'FDate'
    AND type.Value >= CDate('21/02/2014') 
    AND type.Value <= CDate('24/02/2014')

I also attempted using BETWEEN and it returns results outside of the range.

SELECT o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, 
       o.Name, type.Property, type.Value
    FROM ((t_object as o
           INNER JOIN t_objectproperties as location
             ON o.Object_ID = location.Object_ID)
           INNER JOIN t_objectproperties as type 
             ON o.Object_ID = type.Object_ID)
    WHERE location.Property = 'Location' 
      AND location.Value = 'ND'
      AND type.Property = 'FDate'
      AND type.Value BETWEEN CDate('21/02/2014') 
      AND <= CDate('24/02/2014')

Upvotes: 0

Views: 156

Answers (2)

Mark C.
Mark C.

Reputation: 6450

So, I tried this, and it worked. You can convert type.Value to a Date and then Format() it to a Short Date, and pass your parameters as such. No need to mess with a date format that Access doesn't really care for. Just format it.

SELECT t1.ID, t1.dateString
FROM Table1 AS t1
WHERE Format(CDate(t1.dateString), "Short Date") < Format(#03/31/2014#, "Short Date");

dateString is a Text field (like you said yours was) and the values I tested with are:

+-------------+
|  10/01/2014 |
+-------------+
|  20/02/2014 |
+-------------+
|  31/03/2014 |
+-------------+

Here's the SQL for using BETWEEN:

   SELECT t1.ID, t1.dateString
FROM Table1 AS t1
WHERE Format(CDate(t1.dateString), "Short Date")
 Between
               Format(#03/30/2014#, "Short Date")
 AND  
               Format(#01/01/2014#, "Short Date")

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

The problem with date strings is that dates like 01/02/2014 can be interpreted as dd/MM/yyyy or MM/dd/yyyy. Better store the dates in reverse format: yyyy/MM/dd. I have never seen a format like yyyy/dd/MM. Therefore it should be safe with respect to day and month confusion.

An additional advantage of the reverse format is that its string representation compares the same way as its date representation. I.e. sorting strings in yyyy/MM/dd format yields a correct order. Therefore it is not necessary to convert them to dates for the comparison.

Try to select Month(CDate(type.Value)) and see if this really always returns months or whether it sometimes returns days. If days are returned, you could try to convert the properties like this

DateSerial(Mid$(type.Value, 7)  , Mid$(type.Value, 4, 2), Mid$(type.Value, 1, 2))

Upvotes: 1

Related Questions