Month and day reversed in query

[highandwild]

Hi

I have a tabular form displaying accounts transaction data and two
date selectors on the header. Users select the period from and to date
and filter the data using a button.

I make up the WHERE clause using the following:

strWhere = "[TransactionDate] Between #" & Format(DateSerial(Year(Me.txtFromDate), Month(Me.txtFromDate), Day(Me.txtFromDate)), "dd/mm/yyyy") & "# And #" & _
Format(DateSerial(Year(Me.txtToDate), Month(Me.txtToDate), Day(Me.txtToDate)), "dd/mm/yyyy") & "#"

but the filter does not. I write a query definition using the where clause but this turns the month and the day around where an invalid date is not created.

e.g. 04/07/2012 becomes #07/04/2012#

My pc settings are dd/mm/yyyy and the table data displays alright when browsed.

Any ideas what I need to do?

Thanks

Justin

 

[NickHa]

Dates in queries are always #mm/dd/yyyy#, irrespective of your locale. If you change your Format statements around to "mm/dd/yyyy" it should work.

 

[vbaInet]

In this format...

Format(DateSerial(...), "\#mm\/dd\/yyyy\#")

 

[highandwild]

Thanks vbaInet

I managed to avoid the inteligence!! that that Excel applies to such data and
imported the source csv file directly into Access. Access treasts it as text and not as a fraction / decimal which is what I needed. I then use a lookup table to convert to a proper date.

Justin