If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
rst.findfirst not working with two conditions
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 -- from chanakya |
#2
|
|||
|
|||
rst.findfirst not working with two conditions
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "chanu" wrote in message ... 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 -- from chanakya |
#3
|
|||
|
|||
rst.findfirst not working with two conditions
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 -- from chanakya |
#4
|
|||
|
|||
rst.findfirst not working with two conditions
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" wrote in message ... 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 -- from chanakya |
#5
|
|||
|
|||
rst.findfirst not working with two conditions
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. -- from chanakya |
#6
|
|||
|
|||
rst.findfirst not working with two conditions
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! -- from chanakya |
#7
|
|||
|
|||
rst.findfirst not working with two conditions
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "chanu" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|