Problem with Reading Date Field

For four Years I have been using this statement. Yesterday it stopped
working and I do not know why.

rst = db.openrecordset("SELECT * FROM STUDENTS WHERE TstCde = 'B' ORDER BY
LstTstDt, StudentID)

Now all of a sudden it just goes to end of file. After four days of
confusion I removed the Field LstTstDt and it read the file. The contents of
the date field where updated the last time the student took the test. It is
mandatory under our State law that we use this date in this manner! In Short
I cannot use Date Fields in my SELECT SQL Statement.

Does anyone know what has been changed and why it stopped working.

Thank You in Advance

Bill

Steven
Thu Apr 07 11:48:44 CDT 2005

rst =3D db.openrecordset("SELECT * FROM STUDENTS WHERE TstCde =3D 'B' =
ORDER BY=20
LstTstDt ASC, StudentID)

Btw, you really shouldn't be using "Select * ..."

http://aspfaq.com/show.asp?id=3D2096

--=20
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Bill Barber" <BillBarber@discussions.microsoft.com> wrote in message =
news:321D35D3-43F8-4168-916A-80A9F78BAB45@microsoft.com...
> For four Years I have been using this statement. Yesterday it stopped =

> working and I do not know why.
>=20
> rst =3D db.openrecordset("SELECT * FROM STUDENTS WHERE TstCde =3D 'B' =
ORDER BY=20
> LstTstDt, StudentID)
>=20
> Now all of a sudden it just goes to end of file. After four days of=20
> confusion I removed the Field LstTstDt and it read the file. The =
contents of=20
> the date field where updated the last time the student took the test. =
It is=20
> mandatory under our State law that we use this date in this manner! =
In Short=20
> I cannot use Date Fields in my SELECT SQL Statement.
>=20
> Does anyone know what has been changed and why it stopped working.
>=20
> Thank You in Advance
>=20
> Bill

Bob
Thu Apr 07 12:19:18 CDT 2005

Bill Barber wrote:
> For four Years I have been using this statement. Yesterday it stopped
> working and I do not know why.
>
> rst = db.openrecordset("SELECT * FROM STUDENTS WHERE TstCde = 'B'
> ORDER BY LstTstDt, StudentID)

You're missing the :Set" keyword. The DAO OpenRecordset method returns a
recordset _object_. ou need to use the "Set" keyword when assigning objects
to variables:

Set rst = db.openrecordset("SELECT * FROM STUDENTS WHERE TstCde = 'B'
ORDER BY LstTstDt, StudentID)

>
> Now all of a sudden it just goes to end of file.

It does? You don't get an error when attempting to access any of the
recordset properties? What do you mean by "goes to end of file"?

> After four days of
> confusion I removed the Field LstTstDt and it read the file.

You mean you removed it from the ORDER BY clause of the sql statement?

> The
> contents of the date field where updated the last time the student
> took the test. It is mandatory under our State law that we use this
> date in this manner! In Short I cannot use Date Fields in my SELECT
> SQL Statement.

That's a wild leap: from removing a field from one query to make it work to
" ... cannot use Date Fields in my SELECT SQL Statement." How did you come
to this conclusion?

Are you using On Error Resume Next to suppress errors? If so, comment it out
so you can see what error is actually occurring.

Also, you should open your database in Access (if you are using an Access
database) and verify that the sql statement shown above returns the expected
records.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.