 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2005if loop with "<=" condition doesn't work with Records
this is my table structure lower upper price 100 200 $7.20 200 300 $8.10 300 400 $10.90 from here i want to extract the price depend the input limit value for eg:// if the limit value entered in textbox is 150 .it should navigate the table and give the correct price for this limit value $7.20 my code is: Do Until rstAISI4140NQT.EOF If Txtlimit.Value <= rstAISI4140NQT("upper") And Txtlimit.Value >= rstAISI4140NQT("lower") Then Txtpkg.Value = rstAISI4140NQT("price") MsgBox (" price calculated ") Exit Do Exit Sub End If rstAISI4140NQT.MoveNext Loop by this code i cannot get the price value..i found frm this code that operator it doesnot check the"<=" condition "<=" doesnot work in this looping.. plz give me some solution niyaz@flmetal.com.sg Riyaz, This is a better way: Dim rs As DAO.Recordset Dim sSQL As String sSQL = "SELECT price " & _ "FROM myTable " & _ "WHERE " & Txtlimit.Value & " BETWEEN [upper] AND [lower]" Set rstAISI4140NQT = DBEngine(0)(0).OpenRecordset(sSQL, dbOpenSnapshot) If rstAISI4140NQT.AbsolutePosition > -1 Then Txtpkg.Value = rstAISI4140NQT!price MsgBox (" price calculated ") Else MsgBox (" price not found ") End If rstAISI4140NQT.Close Set rstAISI4140NQT = Nothing Regards, Graham R Seach Microsoft Access MVP Sydney, Australia --------------------------- > this is my table structure > [quoted text clipped - 38 lines] > plz give me some solution > niyaz@flmetal.com.sg The poster would need to change his table if you are going to use BETWEEN. For example 200 would match two rows in his table. Your code would work, but it would return two rows in this case. Since the query has no order by clause it is __possible__ that it could return different values on subsequent runs. Not probable, but it is possible. > Riyaz, > [quoted text clipped - 65 lines] >> plz give me some solution >> niyaz@flmetal.com.sg John, What you say is true, however, the poster's code (as is) merely gets the first row that matches the criteria, and as we know, the term "first" is rather arbitrary. He could have used DLookup, to similar effect. At least with a recordset, he has the opportunity to sort the result. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia > The poster would need to change his table if you are going to use BETWEEN. > For example 200 would match two rows in his table. Your code would work, [quoted text clipped - 73 lines] >>> plz give me some solution >>> niyaz@flmetal.com.sg Graham, I hope I didn't sound critical. I just meant to alert the poster that he/she might need to alter the code a bit or alter the table. In other words, the poster needed to make a change and needed to decide which way to eliminate the possibility of ambiguous results. Perhaps I should have gone into further detail and offered a specific solution. Thanks for your understanding, John Spencer > John, > [quoted text clipped - 86 lines] >>>> plz give me some solution >>>> niyaz@flmetal.com.sg <<I hope I didn't sound critical.>> Not an issue. I should have explained that myself. Regards, Graham R Seach Microsoft Access MVP Canberra, Australia > Graham, > I hope I didn't sound critical. I just meant to alert the poster that [quoted text clipped - 98 lines] >>>>> plz give me some solution >>>>> niyaz@flmetal.com.sg > Txtpkg.Value = rstAISI4140NQT("price") txtpkg.value = dlookup("price", "MyTable", "[lower] < " & textbox.value & " AND " & _ textbox.value <= "[higher]") Note that the Value property can handle the NULL value without error if the textbox is out of range. Note also that you have to get the inequality operators right. Hope that helps Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.