CODEWITHSUNDEEP
sqlms-accessdatetime
hacket
hacket

Reputation: 1161

data type mismatch in criterion expression for DateValue function

I have a query that bases its selection on a date field. The problem is that this date field is sometimes null and so just checking for null using 'and not null' won't work.

Here is what I have developed:

SELECT DISTINCT WS_ALL_OBJ.[Owner ID], Users.CHARGE_UNIT, WS_ALL_OBJ.[Owner Name]
FROM WS_ALL_OBJ LEFT JOIN Users ON WS_ALL_OBJ.[Owner ID] = Users.CNAME
WHERE 
    (IIF(IsNull(WS_ALL_OBJ.[Last Modified]), DateValue('2050-12-01'), DateValue(Left(WS_ALL_OBJ.[Last Modified], 10))) < #2012-11-26#
   And 
    (Users.CHARGE_UNIT <> 'CQ'))
GROUP BY WS_ALL_OBJ.[Owner ID], Users.CHARGE_UNIT, WS_ALL_OBJ.[Owner Name];

When running this query it throws a 'Data type mismatch in criterion expression' error message.

I have narrowed it down to this line of code - DateValue(Left(WS_ALL_OBJ.[Last Modified], 10)) because if I substitute this - DateValue('2011-11-26') - it works just fine.

At first I thought it was because there are null values in my Last Modified field which is why I came up with this - (IIF(IsNull(WS_ALL_OBJ.[Last Modified]), DateValue('2050-12-01'), DateValue(Left(WS_ALL_OBJ.[Last Modified], 10))) < #2012-11-26#

Shouldn't that solve the problem of nulls?

The other possibility is that the string coming from Left() is not in the right format. The reason I think this is that this - DateValue('2011-11026') - (the wrong format) will also through the Data Type Mismatch exception.

However, this is what all the data looks like:

2008-01-18 13:10:54 CST

It is stored as a 'Text' field and comes from a linked csv file. The Left() function should take the first 10 characters and pass to DateValue()

I am at a loss and appreciate any help.

Update: I have also tried cdate(Left(WS_ALL_OBJ.[Last Modified], 10)) and get the same error. I have also modified to check for empty strings with `(IsNull(WS_ALL_OBJ.[Last Modified]) or WS_ALL_OBJ.[Last Modified] = '')

I ended up running some queries just to make sure that no WS_ALL_OBJ.[Last Modified] = '' or WS_ALL_OBJ.[Last Modified] = ' ' or the like exist. They all returned zero results.

I have also tried the suggested query structures below, but to no avail.

Upvotes: 1

Views: 3646

Answers (2)

HansUp
HansUp

Reputation: 97131

Consider a subquery to exclude WS_ALL_OBJ rows where [Last Modified] is Null. That way you avoid the error which occurs when trying to cast a Null to a Date/Time value.

SELECT
    w.[Owner ID], u.CHARGE_UNIT, w.[Owner Name]
FROM
    (
        SELECT [Owner ID], [Owner Name], [Last Modified]
        FROM WS_ALL_OBJ
        WHERE [Last Modified] Is Not Null
    ) AS w
    LEFT JOIN Users AS u
    ON w.[Owner ID] = u.CNAME
WHERE 
        DateValue(Left(w.[Last Modified], 10)) < #2012-11-26#
    AND u.CHARGE_UNIT <> 'CQ'
GROUP BY w.[Owner ID], u.CHARGE_UNIT, w.[Owner Name];

Notes:

  1. I used aliases for the table names because that makes the SQL easier for my brain to digest.
  2. Since this is a GROUP BY query, DISTINCT is not needed.
  3. The WHERE clause condition (u.CHARGE_UNIT <> 'CQ') defeats the purpose of the LEFT JOIN. I don't know what's intended to happen, but seems to me you may as well use INNER JOIN instead.

  4. If [Last Modified] may also contain zero-length strings, exclude them in the subquery WHERE:

    WHERE Len([Last Modified]) > 0

Or maybe just use IsDate():

WHERE IsDate([Last Modified]) = True

Or ...

WHERE IsDate(Left([Last Modified]), 10) = True

BTW, you likely already noticed this would be easier with [Last Modified] as Date/Time rather than text data type. I assume you're stuck with text for some reason. But, if not, consider changing it.

Upvotes: 1

HK1
HK1

Reputation: 12230

Here's how I usually handle this. I took the liberty to use aliases to shorten the SQL and make it more readable. I strongly recommend against using spaces in your field names. If you would stop that practice you wouldn't have to worry about using brackets.

I'm also guessing that you are storing date values as strings. I generally try to avoid that. I think in most cases it's better to convert date values to strings when needed than the other way around.

Make sure you leave the vDate argument in the function below as a variant data type. If you change it to string or date it will not allow you to handle nulls. Instead of getting errors your SQL produces really strange results.

Are you sure that your dates are always 10 characters? Date values can be expressed without leading zero's which makes them shorter.

SELECT DISTINCT w.[Owner ID], u.CHARGE_UNIT, w.[Owner Name]
FROM WS_ALL_OBJ as w LEFT JOIN Users as u ON w.[Owner ID] = u.CNAME
WHERE 
    GetLastModDate(w.[Last Modified]) < #2012-11-26#
   And 
    u.CHARGE_UNIT <> 'CQ'
GROUP BY w.[Owner ID], u.CHARGE_UNIT, w.[Owner Name];


'Put this code in a VBA Module
Public Function GetLastModDate(vDate) as Date
    If Nz(vDate, "") = "" Then
        GetLastModDate = #01/12/2050#
    Else
        GetLastModDate = DateValue(Left(vDate), 10)
        End If
End Function

Upvotes: 0

Related Questions