Using ColdFusion with a stubborn MS Access Date/Time field

Sat, 20 Mar 2010 17:16:00 -0700

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!

SQL view error "more column names specified than columns defined"

Tue, 20 Jun 2006 15:29:00 -0700

If you ever get a ColdFusion error message such as the following: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]View or function 'dbo.myviewname' has more column names specified than columns defined. You/somebody probably just added or deleted a column from the view definition. Then, if you have a cfquery that does a "select *" against the view, that error will result. The fix is to run the following system stored procedure in SQL Query Analyzer, to refresh the view: sp_refreshview 'myviewname'

Doing a Subquery in SQL

Thu, 23 Jun 2005 10:00:00 -0700

For my own future reference... This will lookup a value (emailaddress) from table2, to use in where clause in table1.


select *
from table1
where EmailAddress = (select EmailAddress from table2 where UserID = '#URL.UID#')

WebDH blog - SQL

Using ColdFusion with a stubborn MS Access Date/Time field

SQL view error "more column names specified than columns defined"

Doing a Subquery in SQL