rst.findfirst not working with two conditions

[chanu]

i have a table with two date fields and i opened a dao recordset (based on a
query) on it .Now i want to search whether a particular date group falls
between these dates and the rate for that period.Dates in UK style.
FromDate ToDate Rate
01/01/2009 31/03/2009 7570
01/05/2009 30/11/2009 7770
01/12/2009 31/12/2009 7970
//variables declared here
//SQL is defined here to limit it the recordset
Set rst = currentdb.OpenRecordset(strSQL, dbOpenDynaset)
MyFromDAte = 01/06/2009
MyToDate = 31/10/2009
rst.Findfirst "[FromDate] <= #" & MyFromDate & "# And [ToDate] >= #" &
MyToDate & "#"
debug.print rst!RAte
Instead of returning the second row, the findfirst property is returning the
first row (i.e.7570) only though 31/10/2009 is greater than 31/03/2009
If i change it to 'rst.FindLast',it is returning it is returning the last
row(i.e.7970) which is also wrong.Why is this happening so? though my code is
correct. Please help me

 

[Allen Browne]

You need to format the literal date as expected by JET (not as per your
regional date format):

Const strcJetDate = "\#mm\/dd\/yyyy\#"
Dim strWhere As String

strWhere = "([FromDate] <= " & Format(MyFromDate, strcJetDate) & _
") And ([ToDate] >= #" &
Format(MyToDate, strcJetDate) & ")"
rst.Findfirst strWhere

More info in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[chanu]

Hi Allen,
First of all i must thank you because i used your tips and found them
useful.Now i get an opportunity to thank you here. It is really thrilling to
contact a person we want to express our gratitude to comes across all of a
sudden.
The answer you gave to this question is actually throwing ERROR 3077 syntax
error (missing operator in expression). what should i do now? should i also
format the columns in my field. This problem has been pestering me for long.
your solution will do me a lot of service. so give me a suggestion, please

 

[vanderghast]

If that is your real code, then you are due for a surprise:

MyFromDAte = 01/06/2009


is not a date in 2009, but in 1899 !

Proof: In the Immediate Window, try:


? Year(CDate(01/06/2009))
1899


The reason is trivial: / is an operator implying a division:

? 01/06/2009
8.29600132736021E-05


which is something close to zero, and for date (time) zero is 30.12.1899
00:00:00

You probably want to try something such as:

MyFromDAte = #01/06/2009#


(assuming you meant January the 6th, in 2009). You note the #, which
indicates to 'escape' the /, to not consider them as operations to be
performed, and in the end, to try to represent a date in US format. You can
also use:


MyFromDAte = DateSerial(2009, 00, 06)


to avoid any confusion.

And since you merge those dates to strings, take into account Arvin's
comment about forcing the format to be US (otherwise, it will be from your
default date format, which may be replace the intended month by then
intended day, and vice-versa).



Vanderghast, Access MVP

 

[chanu]

thanks a lot Mr.Vandherghast, I have actually tried the same thing with
datediff function and a horrible experience with it. Even the Allen's answer
to my questions also created in my mind another doubt why he has ever used
"\" character. Thanks a lot for enlightening me on this.

 

[chanu]

On close observation, i found the problem is in the code. the problem arises
because of the # character in strWhere when it is already inserted in the
const strcJetDate.
Any how my problem is solved. Thanks a lot and lot, sir!

 

[Allen Browne]

The reason for backslash in:
Const strcJetDate = "\#mm\/dd\/yyyy\#"
is to specify that the next character is a literal. So:
\/
instructs the Format function to treat the slash as a literal. (Without the
backslash, Format() would substitute a dot or dash or whatever character is
defined as the date separator in the Windows Control Panel | Regional
settings.) Hopefully the link gave enough detail on that.

Apologies for leaving the extra # in the string.