I've finally got the solution after a lot of research.
the problem with Access, SQL and dates is that it will query the database in USA format (mm/dd/yyyy) first, no matter if you use locale id, or no matter what your pc regional settings are. This is a recipe for disaster as i found out.
The solution is this function from Giuliano Sauro and a full explanation can be found on 4guysfromrolla.
First make sure you have your locale id on your page to set the date according to your country's format. For me this was uk
Session.LCID = 2057 (dd/mm/yyyy)
Then ensure you have this function from Giuliano..
'Version 1.0 by Giuliano Sauro
If IsDate(dteDate) = True Then
DIM dteDay, dteMonth, dteYear
dteDay = Day(dteDate)
dteMonth = Month(dteDate)
dteYear = Year(dteDate)
IsoDate = dteYear & _
"-" & Right(Cstr(dteMonth + 100),2) & _
"-" & Right(Cstr(dteDay + 100),2)
IsoDate = Null
to insert a date into access database field then format it using the function first....
If IsDate(DatDateAdd) Then
DatDateAdd = IsoDate(DatDateAdd)
the date will now be entered into access in ISO format which will avoid all problems.
To query the database and ensure you get the required recordset back if querying by date then make sure you format your query date in ISO format too, ie query for all records from todays date up until end date (for me xmas 2050)
CurrDate = Date ' get todays date
CurrDate = IsoDate(CurrDate) ' format iso date
strSQL = "Select * FROM events where [date] between #" & CurrDate & "# and #25-12-2050# ORDER BY [date] ASC"
It's solved all my date problems, thanks Giuliano!!