decalration problem - something stupid I think! HELP!

I have this line in VB cobe which works fine,

Set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > CDate('27/Feb/00');")

but I want it to get the last date from another table so I write this

Set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > Date01;")

with declaration:
--
Dim Date01, Date02, Date03, Date04, Date05, Date06 As Date
Dim dbs As Database, Rst As Recordset, Rst2 As Recordset
Dim TotalCount As Integer

Set dbs = OpenDatabase("Cattle.mdb")

' Get government dates from table "Dates"
Set Rst2 = dbs.OpenRecordset("SELECT Period01, Period02, Period03, Period04, Period05, Period06 FROM Dates;")
Date01 = Rst2.Fields(0)
Date02 = Rst2.Fields(1)
Date03 = Rst2.Fields(2)
Date04 = Rst2.Fields(3)
Date05 = Rst2.Fields(4)
Date06 = Rst2.Fields(5)
Rst2.Close
--


BUT IT DOESNT WORK!!!! says something about invalid parameter...

what could be wrong?!?! please help! thanks a lot

Dim Date01, Date02, Date03, Date04, Date05, Date06 As Date
you declared 5 variables as variant and 1 (the last) as Date
correct sintax is
Dim Date01 as Date, Date02 as Date, Date03 as Date, ...and so on


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

whoops... it should work in any case
but I think I find out:
"SELECT count(*) as TotalCount FROM Cattle Where DOB > Date01"
should be replaced with
"SELECT count(*) as TotalCount FROM Cattle Where DOB > " & Date01
or
"SELECT count(*) as TotalCount FROM Cattle Where DOB > " & CDate(Date01)

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

could you send your version of the whole line becuase it is giving me a 'expected end of expression' error....

thanks

set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > " & CVDate(Date01) & ";")





HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

The problem with the below is you need '' surrounding the date:

"SELECT count(*) as TotalCount FROM Cattle Where DOB > " & Date01

instead write

"SELECT count(*) as TotalCount FROM Cattle Where DOB > '" & Date01 & "'"

Try this:

Set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > " & CDate(Date01) & ";")

-Cool Bizs

says runtime error '18' unkknown.

says runtime error '8' unknown...

???

what can I do?!

error message...Runtime error '18' unknown...

any ideas?

Could you please post the full error message and also your code again? We will try to check it out from there.

-Cool Bizs

Set a breakpoint at the top of the area where you suspect the error is happening. Turn off error handling if you have it turned on (ie comment out any On Error statements) run the project and step through the code when the IDE breaks into debug mode. This should tell you which line is causing the problem.

Not sure this will help, but I would check value of date after Cdate, to see if it is DataBaseField compatible.

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

If I'm correct (and I'm sure I am) you are using DAO, not ADO. And If I'm correct (and I hope I am) you should encapsulate a date in #, not in ' when using DAO. Try the following:

set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > #" & Date01 & "#")




Please don't shoot me if I'm not correct ;)

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook