Hi,

I have a form used to search for records between curtain dates. In that form
the user types 2 dates, then runs the search using a 'search' button, and he
should see all the records which are between the dates typed.

The controls in the form are:
MinDateAndTime (TextBox)
MaxDateAndTime (TextBox)
RunSearch (Command Button)

I want the user to input the date/time value in the following format:
dd/mm/yyyy hh/mm/ss

so I've put the following input mask for both MinDateAndTime and
MaxDateAndTime:
00/00/0000\ 00:00:00;0;_

Now what I want to do is that each time the RunSearch button is clicked, it
will run a code that alters the view which is the record source of the form
(View is named: View1), then requery, so that the form will contain only the
records which meets the dates restriction.

The code for the RunSearch button is:
(look at the WHERE clause, the rest is just a 'fill'..)

Private Sub SearchButton_Click()
   DoCmd.RunSQL "ALTER VIEW View1 AS " & _
       "SELECT dbo.EVENTS.FREE_TEXT, dbo.EVENTS.mytext, " & _
       "dbo.EVENTS.EVENT_ID, dbo.Z_EVENTS.Z_EVENT_ID, dbo.Z_EVENTS.MYTEXT
AS Expr1, " & _
       "dbo.EVENTS.EVENT_TIME " & _
       "FROM  dbo.EVENTS LEFT OUTER JOIN " & _
       "dbo.Z_EVENTS ON dbo.EVENTS.EVENT_ID = dbo.Z_EVENTS.EVENT_ID " & _
       "WHERE (dbo.EVENTS.EVENT_TIME < " & _
       "CONVERT(DATETIME, " & Chr(39) & Me.MinDateAndTime & Chr(39) & _
       ", 102)) AND (dbo.EVENTS.EVENT_TIME > CONVERT(DATETIME," & _
       Chr(39) & Me.MaxDateAndTime & Chr(39) & ", 102))"
   Me.Requery
End Sub

The problem is that after entering, for example, the following values:
MinDateAndTime: 19/12/2005 12:12:12
MaxDateAndTime: 19/01/2006 12:12:12

when I click the RunSearch button I get the following error message:
Run-time error '242':
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

I've tried looking for the answer in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-c
o_2f3o.asp
but I can't understand what exactly I should put in the SQL command in order
for that to work properly, while still letting the user enter the dates in
the
dd/mm/yyyy hh/mm/ss
format and not in other formats.

I'm using Access2002 with Microsoft SQL Server 7.

Kind Regards,
Amir.