Coverting "text" date into Date Serial

[Stormin]

I have some VBA code that (essentially) takes a user input for a date, and uses that date in an update query SQL.

However the problem I'm having is that my date is in local UK format, but when SQL runs it converts it to US format e.g. 5th Jan 2017 is inputted and stored as 05/01/2017 as Date type ("DD/MM/YYYY"), but when using that in DoCmd.RunSQL update query it will interpret it as 1st May 2017 (01/05/2017).

I want to convert my date to its serial number to avoid this problem e.g. 42740 above and then use this in my SQL update query, which works fine.

However I'm stuck as to how to actually convert it to a serial purely within VBA environment. I'm sure it's simple and that I'm just drawing a blank... apologies if so.

Any ideas?

 

[Ranman256]

once the date is in a Date field (numeric) you can format it anyway you wish.
convert a string date in dd/mm/yyyy layout, via

as mm/dd/yyyy
mid([date],4,2) & left([date],2,2) & "/" & right([date],4)

or arrange the way you need.

 

[missinglinq]

Allen Browne (from Down Under) has an excellent article explaining the ins and outs of dealing with non-US formatted dates in SQL:

International Dates in Access

Linq ;0)>

 

[Stormin]

once the date is in a Date field (numeric) you can format it anyway you wish.
convert a string date in dd/mm/yyyy layout, via

as mm/dd/yyyy
mid([date],4,2) & left([date],2,2) & "/" & right([date],4)

or arrange the way you need.

It's the getting the date, stored as UK format, into the field that is the problem. I know I can do it by doing UK -> US -> insert -> UK but that seems unnecessary to me when I know all date/time is stored as a double.
To me it's analogous to having an input box for £s, then having to convert it to $s, then converting from $s to £s for display.

Allen Browne (from Down Under) has an excellent article explaining the ins and outs of dealing with non-US formatted dates in SQL:

International Dates in Access

Linq ;0)>

I had found that previously in my initial research, was enlightening but unfortunately only presented the solution which I already knew existed (as above, going UK -> US -> insert -> UK).



I was hoping there was a simple easy way to convert the human formats into programmatic formats in VBA (since that is what it actually uses anyway). Hmmm...

 

[gemma-the-husky]

the catch with dates is that in SQL, you need to present them in an unambiguous format. SQL will try to treat a date as US date.

So 2/3/17 will be treated as Feb 3rd, not as March 2nd.

You have to coerce the date into an unambiguous representation.

In the UK, I use format(somedate,"long date"), although others have pointed out this may not work in non-UK locales.

On occasion I have also used cdbl(somedate), and just treated the date as a number, although I think this can give (rounding) errors in the time representation. It was OK for my purposes.

 

[Stormin]

Good idea with using long wordy dates for unambiguity, I hadn't thought of that since I rarely use them in normal applications.

However...

On occasion I have also used cdbl(somedate), and just treated the date as a number, although I think this can give (rounding) errors in the time representation. It was OK for my purposes.

Using CDbl() to convert my date, stored as VBA type Date, into a double is exactly what I was looking for. I hadn't come across those conversion functions before so it's good to add these to my newbie VBA arsenal!

In this case I'm only using the integer part so rounding errors in the double should never arise. It is working as expected in my tests.

Here is the code that now uses this method to take a user input (in this case, it is part of the imported file name which is where the relation is to the import date):

Public Function TableImportDate( _
                TableName As String, _
                Optional RecordsAdded As Long = -9999, _
                Optional ManualDate As Date _
                )
 
    Dim DatetoUse As String
 
    If ManualDate = 0 Then
        DatetoUse = "Now()"
    Else
        DatetoUse = CDbl(ManualDate)
    End If
 
    DoCmd.SetWarnings False 'disable warning popups
    DoCmd.RunSQL "UPDATE TableUpdates SET TableUpdates.[Last Import to Table] = " & DatetoUse & "," & _
                 " TableUpdates.[Num Records Added] = " & RecordsAdded & _
                 " WHERE ((TableUpdates.[TableName])='" & TableName & "')"
    DoCmd.SetWarnings True 'enable warning popups
 
End Function

Note that my input box takes the input string and converts it to a date type, which is then used in the function:

    'Report date selection box
    strInputBox = InputBox("Enter the report date you wish to import from." & vbNewLine & _
                           "Reports can be found at:" & vbNewLine & _
                           strFolderPath & vbNewLine & _
                           vbNewLine & _
                           "Both CAR and LCV reports are imported automatically using the report date." & vbNewLine & _
                           vbNewLine & _
                           "Date:", _
                           strImportDesc & ": Report Date", _
                           Format(Date, "DD/MM/YYYY"))
    If strInputBox = vbNullString Then GoTo Err_Inputbox_Cancel
 
    'Convert from string type to date type
    On Error GoTo Err_Date_Conversion
    datDate_sel = DateValue(Format(strInputBox, "DD/MM/YYYY"))
    On Error GoTo 0

Cheers Dave!

 

[Minty]

I still use Allen Browns SQLDate function most of the time, it's simple to implement and use in VBA query creation ;

Public Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function