US to UK Date format

[LSTC018]

Hi,
I’m complete beginner challenging myself, getting relevant things I want to achieve from the internet and now this forum – I’ve managed to get this VBA below to work to a point. I’ve worked out that my dates which are UK format are treated as US format giving me random results (I can see why) BUT - Can anyone help how I can change this?


_______________________________________________




Option Compare Database

Private Sub Command12_Click()
' search button
Call search
End Sub
Sub search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Or IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus

Else
strCriteria = "([Order Date] between #" & Me.OrderDateFrom & "# And #" & Me.OrderDateTo & "#)"
task = "Select * from Table1 where (" & strCriteria & ") order by [Order Date]"
DoCmd.ApplyFilter task

End If

End Sub

 

[Minty]

This will help hopefully http://allenbrowne.com/ser-36.html

You need to format your dates - I personally would format them as YYYY-MM-DD as it's really easy to read and pretty universally accepted by almost all database systems.

 

[LSTC018]

Hi,


Unfortunately all the tables I’m looking at to get reports from are already set up with DD/MM/YYYY and can't be changed,


Cheers

 

[isladogs]

All you need to do is format the dates as "mm/dd/yyyy" or as "yyyy-mm-dd" for SQL statements:

strCriteria = "([Order Date] between #" & Format(Me.OrderDateFrom,"mm/dd/yyyy") & "# And #" & Format(Me.OrderDateTo,"mm/dd/yyyy") & "#)"

If you want more information, see this recent (lengthy) thread: https://www.access-programmers.co.uk/forums/showthread.php?t=303364

 

[LSTC018]

Bingo - Thats done the job I've been working on that for two days - Cheers

 

[isladogs]

You're welcome. You will need to do this on ALL your SQL statements for the code to work reliably