To read the article online, visit http://www.4GuysFromRolla.com/webtech/011701-1.2.shtml

How To Code For Compatible Date Formats for Access and SQL Server, Part 2

By Doug Dean


Read Part 1


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.

   '////// ESCAPE ANY PERIODS FOR SOME INTERNATIONAL DATES
   intIndex = InStr(strDate, ".")
   Do While intIndex
      strDate = Left(strDate, intIndex - 1) & "-" & _
                Right(strDate, Len(strDate) - intIndex)
      intIndex = InStr(strDate, ".")
   Loop

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.

   PrepDate = strDate
End Function

Calling the PrepDate() 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 PrepDate(Now).

MyDateVar = #1/7/01 2:38:56 PM#
strSql = "INSERT INTO YourTableName (YourDateFieldName) " & _
         "VALUES ('" &  PrepDate(MyDateVar) & "') WHERE MyID = 1"

This line of code will result in a valid SQL statement assigned to the strSql string variable. For example, when I ran the above code, strSql contained the following value:

INSERT INTO YourTableName (YourDateFieldName) VALUES ('{ts '2001-01-07 14:38:56'}') WHERE MyID = 1

Here is an example of a SELECT sql statement using PrepDate():

MyDateVar = #1/7/01 2:38:56 PM#
strSql = "SELECT YourDbField FROM YourTableName " & _
         "WHERE YourDateFieldName = " & PrepDate(MyDateVar)

Which results in the strSql string value below.

SELECT YourDbField FROM YourTableName WHERE YourDateFieldName = {ts '2001-01-07 14:38:56'}

Here's the entire prepDate() code that you can cut and paste it into your VB code!

Public Function PrepDate(DateToConvert As Date, _
                         Optional UseTimeAndDate As Boolean = True) _
                            As String

   '//////// DECLARE TWO METHOD VARIABLES
   Dim strDate As String
   Dim intIndex As Integer

   '/////// ASSIGN A FORMAT BASED ON 'TIME STAMP' OR 'DATE'
   If UseTimeAndDate Then
       '---- DATE & TIME
       strDate = "{ts '" & Format(DateToConvert, _
                              "yyyy-mm-dd hh:mm:ss") & "'}"
   Else
       '----- DATE
       strDate = "{d '" & Format(DateToConvert, "yyyy-mm-dd") & "'}"
   End If

   '////// ESCAPE ANY PERIODS FOR SOME INTERNATIONAL DATES
   intIndex = InStr(strDate, ".")
   Do While intIndex
       strDate = Left(strDate, intIndex - 1) & "-" & _
                 Right(strDate, Len(strDate) - intIndex)
       intIndex = InStr(strDate, ".")
   Loop

   PrepDate = strDate
End Function

Happy Programming!

  • By Doug Dean


  • Article Information
    Article Title: How To Code For Compatible Date Formats for Access and SQL Server, Part 2
    Article Author: Doug Dean
    Published Date: Wednesday, January 17, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/011701-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers