date formatting issue

[iankerry]

Hi

I have an access front end, and an sql server for data.

Dates seem to be stored in sql in this format

2011-10-04 00:00:00.000

but thankfully they appear in our database in the UK format 4/10/2011.

The problem is when i run VBA code from our front end i get mixed results.

For instance users when loading the form can choose which set of dates to have as a filter:

strFilter = "[datefield] >= #" & Format(Me.comboSeason.Column(1), "dd/mm/yyyy") & "# and [datefield] <= #" & Format(Me.comboSeason.Column(2), "dd/mm/yyyy") & "#"

DoCmd.ApplyFilter , strFilter​

This seems to work well (without for format command is doesnt of course.)

BUT THE PROBLEM IS when i run this line to find todays date:
Dim rst As DAO.Recordset
Dim dtTodaysDate As Date
Set rst = Me.RecordsetClone
dtTodaysDate = Date

dtTodaysDate = Format(Date, "dd/mm/yyyy")

rst.FindLast "[dateField] <= #" & dtTodaysDate & "#"

If rst.NoMatch Then
rst.MoveFirst
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Set rst = Nothing

Instead of landing on a date that is the 4th october (4/10/2011), it lands on a date 10th April (10/4/2011). I clearly need to format the date correctly in the code as above, but i cant find anything that works.

Can anyone help?

many thanks.

ian

 

[spikepl]

http://allenbrowne.com/ser-36.html

 

[iankerry]

Hi Spikepl

Thanks for responding. Looks an interesting article and i am sure the answer is in there. My programming skills are not great however, and even when i use # around the dates it still doesnt work or i get type mismatch - e.g. dtTodaysDate = Format(dtTodaysDate, "#dd/mm/yyyy#")

might you know a way of having a line of code that formats dtTodaysDate as a UK date?

I appreciate it.

ian