Excel VBA - SQL & VBA how to select dates in Access while keeping UK date format?

[knifey]

Hi,
I've got a problem with the auto us date conversion 'feature' in MS Access and was wondering if anyone could help. The code below is run from an Excel userform.
The problem is that when the sql executes it is looking for the US version of the date (even though the sql in my immediate window says different).
Can anyone advise of a surefire way of keeping the original UK date format of 'eventsEndDate' when doing a select or insert into in Access?
Any advise or pointers would be much appreciated.
Thanks,
K
P.S. This code has been cut down for posting.

Private Sub UserForm_Activate()
Dim cn As ADODB.Connection
Dim rsLYTo As ADODB.Recordset
Dim dbPath As String
Dim sSQL As String
Dim thisWEdate As Date
Dim eventsEndDate As Date
Dim strLYweEnd As Date
dbPath = wb1.Worksheets("Config").Range("E20").Value 'main db
thisWEdate = GetThisWeeksWeekEndingDate(Format(Date, "dd/mm/yyyy"))
eventsEndDate = thisWEdate + 35 (+ 5 weeks)
Set cn = New ADODB.Connection
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & dbPath & "'" & ";"
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False; Data Source='" & dbPath & "'" & ";"
'get last years latest corrisponding we date
Set rsLYTo = New ADODB.Recordset
sSQL = "SELECT fldLYWeekEnd "
sSQL = sSQL & "FROM tblWEDates"
sSQL = sSQL & " WHERE fldWeekEnd = #" & eventsEndDate & "#;"
rsLYTo.Open sSQL, cn, adOpenDynamic, adLockOptimistic
If Not rsLYTo.EOF And Not rsLYTo.BOF Then
    strLYweEnd = Trim(rsLYTo("fldLYWeekEnd").Value)
    rsLYTo.Close
    Set rsLYFrom = Nothing
End If
Set cn = Nothing
End Sub

 

[SkipVought]

Hi,

A DATE is a DATE!!!

A format is just a display representation of the DATE. Since Bill Gates is a USA guy, the display defaults to m/d/yyyy.

Set your display when yer all done manipulating data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Use international standard date format like this:
sSQL = sSQL & " WHERE fldWeekEnd = #" & Format(eventsEndDate, "yyyy-mm-dd") & "#"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886