A date issue...

[jalldridge]

Hi guys,

I'm using the following code to pull info from messages in outlook.

strContents = msg.Body
    splitstr = Split(strContents, vbCrLf)
    Dim strDate As String
    On Error GoTo errorhandle
    
    'get date of download
    strDate = Trim(Mid(splitstr(4), 8))
    If (Not IsDate(strDate)) Then
        
        Dim splitDate As Variant
        splitDate = Split(strDate)
        strDate = Left(splitDate(2), 2) & " " & splitDate(1) & " " & splitDate(3)
        
    End If
    evalDetails.dtDate = CDate(strDate)

In the above evalDetails is a user defined data type that contains a number of elements - one of whch is a date.

So after getting hold of the body of the email message I need to get hold of the date thats held in the forth data array (splitstr(4)). This will either be a date or something along the lines of the following:

>Sent: Wednesday, June 01, 2005 7:06:36 AM

For ref splitDate is as follows:

splitDate(0) = "Wednesday,"
splitDate(1) ="June"
splitDate(2) ="01,"
splitDate(3) ="2005"
splitDate(4) ="7:06:36"
splitDate(5) ="AM"

So once I have the the date (which at this stage is in UK format), one of the things is to do an update into my database. ANd I use the following to do this:

   Dim strSQL As String
    strSQL = "INSERT into tbl_download([email_address],[name],[company],[download_date],[type_of_product]) " & _
            "VALUES ('" & evalDetails.strEmail & "','" & evalDetails.strFirstName & " " & _
            evalDetails.strLastName & "','" & evalDetails.strCompany & "',#" & CStr(evalDetails.dtDate) & _
            "#,'" & evalDetails.strProduct & "');"

So the issue is that if I look in my database then the date has been entered as US style ie it will be 06/01/2005. not 01/06/2005. Doing debugs during the code shows gives me a UK style date.

How can I get ensure that the date in the Database is in UK style?

The user can do a query against the DB to see info (displayed in a table) and can get confused if the date is not displayed in UK style....

Hope thats clear


Thanks

 

[jalldridge]

OK I dont believe that anything is wrong with my code.

I created a table and then undertook an insert with the following:

docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"

Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005.

If I set up a query via the query wizard to look at this field then I see that the date is shown in US. I tried to use the following:

result: format(dates, "dd/mm/yyyy")

and the date still showed up at US. argghhhh.

Any ideas?

I shall post this in the queries section as its note vba specific.