Using ColdFusion with a stubborn MS Access Date/Time field

Posted on : March 20, 2010 5:16 PM | Posted by : Troy
Related Categories: ColdFusion,SQL

I have a small application using a MS Access database. Yes, I know all the reasons why it shouldn't be used, let's not go there. Fact is, many developers still use Access for small apps, prototyping, etc, and may come across the same roadblock in which I figured out a solution. For reference, today's date is 3/19/2010 which was used in the query examples shown.

Requirement: Query a table of Jobs (JobTitle, Dept, Salary, etc) that contains a Date/Time field named PostingEndDate. This field is configured as Required = No. That means the admin user who populates records in the table will either supply a date, or may leave the date blank. Think of it as a field that allows NULL in MS SQL. On the end-user side, the query needs to list only current jobs by filtering the records where:
1) PostingEndDate has not passed today's date
OR
2) PostingEndDate is empty (blank value means the Job can be displayed to the user indefinitely)

Here is a cfdump (jobList_raw) of all records:

SELECT JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM tblJob
WHERE ActiveJob = 1
ORDER BY JobTitle

Problem: no easy way to write the WHERE clause. Here are some attempts:
1) Len(PostingEndDate) = 0
No error, but does not pick up those with blank dates:

SELECT JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM tblJob
WHERE ActiveJob = 1
AND (Now() <= PostingEndDate OR Len(PostingEndDate) = 0)
ORDER BY JobTitle

2) PostingEndDate = ''
Generates error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
We can't compare the date field to an empty string.

SELECT JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM tblJob
WHERE ActiveJob = 1
AND (Now() <= PostingEndDate OR PostingEndDate = '')
ORDER BY JobTitle

3) Cstr(PostingEndDate) = ''
Generates error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid use of Null
This was my attempt to Cast the date to a string using an Access function. Guess not.

SELECT JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM tblJob
WHERE ActiveJob = 1
AND (Now() <= PostingEndDate OR Cstr(PostingEndDate) = '')
ORDER BY JobTitle

Solution: write a ColdFusion Query of Query (QoQ) to UNION the two conditions into one resultset. Here are the steps that lead to the final solution.
1) I still need a way to get a string representation (varchar) of the date field. So I added another column (PostingEndDate_str) to the Query object.

2) Loop query and populate the new varchar field. This was intended to produce a blank string for what cfdump showed as [empty string]. Then I should be able to use: PostingEndDate = ''

NOPE! Still see [empty string] in the new field, THIS BECAME THE HAIR PULLING MOMENT OF THE SOLUTION AT THIS POINT, SO I STARTED GOOGLE SEARCHING.

I decided to try using '-' before and after the value, a trick I noticed in Ben Nadel's blog:

Now I see "--" for all those [empty string] values, much better. I can work with that in the QoQ.

3) Last is the Query of Query UNION.

SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM jobList_raw
WHERE #ParseDateTime(DateFormat(Now(),'mm/dd/yyyy'))# <= PostingEndDate 
UNION
SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM jobList_raw
WHERE     PostingEndDate_str = '--' 
ORDER BY JobTitle

Here is the final recordset. It correctly leaves the Architect job filtered out because its PostingEndDate of Mar 9, 2010 has passed. The Mortgage Processor and Supervisor jobs with blank dates are kept in the results!