Access Date Format Issues

I have been asked by a few people how to solve the issue that when you are sending data from Excel to Access (and vice versa) in VBA the dates get flipped into US format :- MM/DD/YYYY instead of DD/MM/YYYY now this will only happen when you the day of the date is less than 12, the issue comes from the way the Excel or Access interperate the date it recieves. annoyingly it does not give any regard to the regional settings.

Anyway the way I found to get around it is using a function to convert the date into a format that can not confuse the system.

Public Function Fixdate(ByVal str)

Dim dteDay
Dim dteMonth
Dim dteYear

If isnull(str) Then
msgbox "Error"
Exit Function
End If

dteDay = Day(str)
dteMonth = MonthName(Month(str), True)
dteYear = Year(str)

FixDate = dteDay & "-" & dteMonth & "-" & dteYear

End Function

This is then used as follows

myDate = fixdate(date)

This will return a value to mydate as 25-May-2010 which Excel or Access will be able to interperate as the correct date.