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
Re: decalration problem - something stupid I think! HELP!
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.
Re: decalration problem - something stupid I think! HELP!
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.
Re: decalration problem - something stupid I think! HELP!
could you send your version of the whole line becuase it is giving me a 'expected end of expression' error....
thanks
Re: decalration problem - something stupid I think! HELP!
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
Re: decalration problem - something stupid I think! HELP!
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 & "'"
Re: decalration problem - something stupid I think! HELP!
Try this:
Set Rst = dbs.OpenRecordset("SELECT count(*) as TotalCount FROM Cattle Where DOB > " & CDate(Date01) & ";")
-Cool Bizs
Re: decalration problem - something stupid I think! HELP!
says runtime error '18' unkknown.
Re: decalration problem - something stupid I think! HELP!
says runtime error '8' unknown...
???
what can I do?!
Re: decalration problem - something stupid I think! HELP!
error message...Runtime error '18' unknown...
any ideas?
Re: decalration problem - something stupid I think! HELP!
Could you please post the full error message and also your code again? We will try to check it out from there.
-Cool Bizs
Re: decalration problem - something stupid I think! HELP!
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.
Re: decalration problem - something stupid I think! HELP!
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.
Re: decalration problem - something stupid I think! HELP!
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