Friday, June 8, 2007

Date Delimiter for Access and SQL Server

Microsoft's two most popular databases (MS Access & MS SQL Server) implement date delimiter differntly. This makes it hard to write queries that are compatible with both.

What I normally do this in situation would be creating a flag to indicate which database is being used and use the appropriate delimiter as follow:

Dim bAccessInUse As Boolean
Dim sSql As String

Function DateOut(byVal TheDate as String) as String
If bAccessInUse Then

Return "#" & TheDate & "#"
Else Return "'" & TheDate & "'"
End If
End Function

sSQL = "Select * From tablename Where date_col=" & DateOut(the_date)

If you use this function throughout your code and set and the flag by checking the connection string, you should have no problem making it run against either database without any modification.

1 comment:

Anonymous said...

Does this work for Null date values?