Error 3077 Recordset.FindFirst using dates

[chanu]

I am using Access2007 and VB 98 . I have a table whose model is as follows:
tblPays
FromDate ToDate MonthlyPay
01/03/2008 29/02/2008 7770
01/03/2009 28/02/2009 7970
01/03/2009 28/02/2010 8300
The dates are formatted in Indian Style(dd-mm-yyyy)
I have a function now to find the monthly pay rate for a particular period.
For example, the period 01-10-2008 To 31-12-2008 should give the monthly
rate: 7770
Function MyPay(datFromDate,datToDate)
Dim db AS Dao.Database
Dim rst AS Dao.Recordset
Set rst = db.openrecordset(tblpays,dbopendynaset)
“ Now I want to find the monthly pay rate for 01/04/2009 to 30/06/2009
“And the code I used is:
Rst.FindFirst "FromDate<= datFromDate And ToDate > = datToDate"
'it showed Run Time Syntax Error 3077
'Again, I tried with the following code:
Rst.FindFirst "FromDate<= #datFromDate# And ToDate > = #datToDate#"
***SAME ERROR
'My next attempt is :
Rst.FindFirst "FromDate<= #"&datFromDate& "# And ToDate > = #"&
datToDate& "# "
'Same RunTime Error 3077 missing syntax.(I formatted the dates in US style
also. No use!!!)
Can any one please help me with this? It is a key part of my code.Because
all the remaining calculations are dependent on this. Please Be more
explanatory. I am totally new to programming..

 

[Tom van Stiphout]

On Sat, 5 Dec 2009 05:45:01 -0800, chanu

This one is the closest:
Rst.FindFirst "FromDate<= #datFromDate# And ToDate > =
#datToDate#"
but you need to understand that you're passing a string, and that
FindFirst has no way to interpret your variable names into values. So
it concludes that "datFromDate" is an illegal date.
Try this:
Rst.FindFirst "FromDate <= #" & datFromDate & "# And ToDate >= #"
& datToDate & "#"
Now the dates are being inserted as their values, not their variable
names.

-Tom.
Microsoft Access MVP

 

[chanu]

Thanks a lot Mr.Tom Your suggestion helped me a lot