Back to Index |

How to handle the MS Access DateTime field

MS Access DateTime fields can be strange to work with. You cannot simply build a SQL statement with a simple date such as '01/01/2004'. To MS Access this is a string and you will receive a Datatype mismatch error, whereas most other databases would recognize this as a Date and insert or update the column appropriately.

For MS Access databases, you have to wrap your DateTime fields in a # sign. For instance, #01/01/2004#. BlueSky Integration Studio is designed to recognize this and automatically reformat the SQL statement for MS Access Databases!

In your DateTime columns, double-click on the Join object, then click the code button for your DateTime column mapping and wrap your column in the following code:

For instance, if your column name is a1.MyDate

If a1.MyDate = "" Then
Return Nothing ' This will format the SQL statement to set the column to ANSI Standard NULL
Else
Return "#" + a1.MyDate + "#"
End If

© 2003 - 2007 Relational Solutions, Inc. - All rights reserved