Problem with date in a form search function

[Sarge]

Hi, I have a problem with a search function on a form that bookmarks a record for a date. The date is enterred into a text box called "EnterDate" and then a button is selected to start the search function. This works OK except that the format for the date is being changed somewhere along the line. I am in Australia and therefore the dates are based on DD/MM/YYYY. But when the search function finds the record it is formatted as MM/DD/YYYY. eg. if I search for 2nd April 2011 I get 4th of February 2011 returned?? I believe this is a common fault with Access, but my knowledge of VBA code is not that great (still learning) and I can't work out how to retrieve the correct date. If someone can point out the changes needed in the code to return the correct date I will be very greatfull.

This is the code I am currently using:


Private Sub Findrecord_Click()
Dim Findrecord As DAO.Recordset
Set Findrecord = Me.RecordsetClone
Findrecord.FindFirst "[Date] = #" & Me.EnterDate & "#"
If Findrecord.NoMatch Then
MsgBox "Name Date Not Found"
Else
Me.Bookmark = Findrecord.Bookmark
End If
End Sub

 

[Taruz]

Hi..

Try this way..:


Dim Findrecord As DAO.Recordset
Set Findrecord = Me.RecordsetClone
Findrecord.FindFirst "cdbl([Date]) = " & cdbl(Me![EnterDate])
If Findrecord.NoMatch Then
MsgBox "Name Date Not Found"
Else
Me.Bookmark = Findrecord.Bookmark
End If

 

[stopher]

Try:

Findrecord.FindFirst "[Date] = #" & FORMAT(Me.EnterDate,"dd/mm/yyyy") & "#"

 

[Sarge]

Taruz,

A million thankyou's!! Worked like a charm...

I hope to complete some training on basic VBA coding soon to get a better understanding.

In the meantime I have to rely on kind poeple like you. Thanks again..

Sarge.