By Greg Carter.

Item 11 in [the November 2, 2002] VBHelper email talks about how "Access uses # to delimit dates while SQL Server uses quotes." I recently ran into that problem when converting a VB6 application from Access to SQL Server.

Having made the mistake of coding the pound signs into my SQL code, however, I was leery of changing them to quotes. I may someday want to use Access again, if I sell this application to a smaller company, or offer a self-contained trial version.

So, I ended up defining a global variable and referencing it throughout the application whenever I build a SQL query which uses literal dates. Thus, this:

.Source = "SELECT * FROM tblOrder " & _ "WHERE OrderDate = #" & strOrderDate & "#"

became this:

.Source = "SELECT * FROM tblOrder " & _ "WHERE OrderDate = " & gstrDateDelimiter & _ strOrderDate & gstrDateDelimiter

I also ran into a couple of other tricky issues in converting from Access to SQL Server involving the DATEPART function and Boolean values.

When using DATEPART in SQL Server (T-SQL), the datepart parameter, such as "yyyy", must not be delimited with quotes; in Access, single-quotes are required to delimit the value. Again, I used a global variable to solve this problem, so that this:

.Source = "SELECT * FROM tblOrder " & _ "WHERE DATEPART('yyyy',OrderDate) = 2002"

was changed to this:

.Source = "SELECT * FROM tblOrder " & _ "WHERE DATEPART(" & gstrSQLDatePartDelimiter & _ "yyyy" & strSQLDatePartDelimiter & ",OrderDate) = 2002"

Finally, since SQL Server does not have a Boolean data type, but uses the "bit" data type instead, all of my Access SQL queries which referred to Boolean values needed to be modified. I wrote several small globally-available functions to translate between True/False and 1/0. So, for example, this Access-oriented code:

.Source = "SELECT * FROM tblOrder " & _ "WHERE RushOrder = True"

now reads:

.Source = "SELECT * FROM tblOrder " & _ "WHERE RushOrder = " & ConvertBooleanToText(True)

The database selection is made in the Sub Main routine:

    ' Hard-coded selection of database
    ' S = SQL Server
    ' A = Access
    strDatabaseMode = "S" 

The ConvertBooleanToText function looks like this:

Public Function ConvertBooleanToText(blnValue As Boolean) As String If strDatabaseMode = "S" Then ' SQL Server If blnValue = True Then ConvertBooleanToText = "1" Else ConvertBooleanToText = "0" End If Else ' Access If blnValue = True Then ConvertBooleanToText = "True" Else ConvertBooleanToText = "False" End If End If End Function

I could have used global variables here too, but there are situations in which the True or False value is not fixed, but is dependent, for example, on whether a CheckBox on a form is checked or unchecked, and this seemed to make for more readable code. Your mileage may vary.

I hope this helps someone else avoid the learning process I had to go through!