if loop with "<=" condition doesn't work with Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
(e-mail address removed)
 
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
 
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.

Graham R Seach said:
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
---------------------------

riyaz said:
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
(e-mail address removed)
Click to expand...
Click to expand...
 
Txtpkg.Value = rstAISI4140NQT("price")
Click to expand...

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
 
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


John Spencer said:
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.

Graham R Seach said:
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
---------------------------

riyaz said:
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
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
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

Graham R Seach said:
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


John Spencer said:
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.

Graham R Seach said:
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

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
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
<<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


John Spencer said:
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

Graham R Seach said:
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


John Spencer said:
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,

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

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
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top