How To Code For Compatible Date Formats for Access and SQL Server, Part 2By Doug Dean
In Part 1 we looked at the basics for our function that converts a standard date/time value into an Access and SQL-ready date/time format. In this part we'll look at protecting ourselves against periods in certain date formats and (finally) present the complete code of the function!
Extra Code for Period Problems
That would be the full extent of the code except for some date formats that utilize periods in place of slashes. To avoid this problem, five lines of code will check for periods within the date string and substitute slashes if found.
Now all that is left to do is assign the
strDate variable, which holds our
formatted date string, to the method name so it can be sent back to the calling method.
Below you'll find an example of calling this method from a VB DLL. The following line uses the
PrepDate() method to create a date-safe SQL statement.
It does this by inserting the formatted date string into a type Date database field
YourDateFieldName) of a valid sql statement. This INSERT sql statement can be
used with ADO via ODBC for either Access or SQL Server. The
#1/7/01 2:38:56 PM#
date is used, but any valid date can be used, like
This line of code will result in a valid SQL statement assigned to the
string variable. For example, when I ran the above code,
strSql contained the
Here is an example of a SELECT sql statement using
Which results in the
strSql string value below.
Here's the entire
prepDate() code that you can cut and paste it into your VB code!