The openrecordset method expects a string as the SQL, so it interprets
[Forms]![myform ... as just part of that string and doesn't get the value of
the textbox. To get the value of the textbox in there, you have to do this:
sest rst= dbs.OpenRecordset("Select * from ATest  Where Date > " &
[Forms]![myForm].txtDate)
hope that helps
-John

Came cross an article below that sorted out the problem.
URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/
sa00e19.asp

The answer is that you're invoking the Jet engine in a different
context here, and that makes all the difference. When you get data from
a parameter query that uses a form to supply the parameter via the
Access user interface, as in the earlier example, Access can evalute
the expression involved and supply a value to Jet. When you get data
from a parameter query that uses a form to supply the parameter via
VBA, instead of through a form, the bits of Access that manage user
interface matters aren't involved. Consequently, Jet is passed the
string "[Forms]![frmSelectCountry]![cboCountry]" instead of the value
in cboCountry. Because Jet doesn't know how to evaluate the
expression, it can't open the recordset.

Thanks

Database is a DAO object. By default, Access 2000 uses ADO. You've obviously
set a reference to Microsoft DAO 3.6 Object Library, or else the declaration
"Dim dbs As Database" would have raised an error, but when you did so, you
must not have unchecked the reference to Microsoft ActiveX Data Objects 2.1
Library (ADO)

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rst as DAO.Recordset (to guarantee an ADO recordset,
you'd use Dim rst As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

You need to wrap the text control with the date delimiter #, and also
concatenate it into the where clause string, so that Access looks for
the Data within the control and not the literal text
"[Forms]![myForm].txtDate ".

(Select * from ATest Where DateField > #" & [Forms]![myForm].txtDate &
"#")

The form MyForm must be open.
If this code is placed in the form MyForm itself, you can use:
Where Date > #" & Me!txtDate & "#")

Note: Do you really have a field named date?
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312  'Reserved Words in Microsoft Access'  for Access 97
209187  'ACC2000: Reserved Words in Microsoft Access'
286335  'ACC2002: Reserved Words in Microsoft Access'
321266  'ACC2002: Microsoft Jet 4.0 Reserved Words'