Using ColdFusion with a stubborn MS Access Date/Time field
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:
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:
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.
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.
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 = ''
<cfset QuerySetCell(jobList_raw, "PostingEndDate_str", "#PostingEndDate#",currentrow)>
</cfloop>
I decided to try using '-' before and after the value, a trick I noticed in Ben Nadel's blog:
<cfset QuerySetCell(jobList_raw, "PostingEndDate_str", "-#PostingEndDate#-",currentrow)>
</cfloop>
3) Last is the Query of Query UNION.
FROM jobList_raw
WHERE #ParseDateTime(DateFormat(Now(),'mm/dd/yyyy'))# <= PostingEndDate <!--- Lefthand expression will format Now() as: 2010-03-19 00:00:00.0 --->
UNION
SELECT JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM jobList_raw
WHERE PostingEndDate_str = '--' <!--- [empty string] dates will contain this value from the QuerySetCell loop executed above --->
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!
