query from more than 1 fields

[royaloba]

Code    Range1      Range2     Range3    Range4       Range5     Range6    
A    $0.00      $10.42     $52.08    $177.08       $468.75    $1,041.67    
B    $0.05      $0.10         $0.15    $0.20       $0.25      $0.30    
HF    $521.00      $729.00    $1,146.00    $1,979.00  $3,438.00  $5,729.00    
HF1    $688.00      $895.00    $1,313.00    $2,146.00  $3,604.00  $5,896.00    
HF2    $854.00      $1,063.00  $1,479.00    $2,313.00  $3,771.00  $6,063.00    
HF3    $1,021.00 $1,229.00  $1,646.00    $2,479.00  $3,938.00  $6,229.00    
HF4    $1,188.00 $1,396.00  $1,813.00    $2,646.00  $4,104.00  $6,396.00    
ME    $667.00      $875.00    $1,292.00    $2,125.00  $3,583.00  $5,875.00    
ME1    $833.00      $1,042.00  $1,458.00    $2,292.00  $3,750.00  $6,042.00    
ME2    $1,000.00 $1,208.00  $1,625.00    $2,458.00  $3,917.00  $6,208.00    
ME3    $1,167.00 $1,375.00  $1,792.00    $2,625.00  $4,083.00  $6,375.00    
ME4    $1,333.00 $1,542.00  $1,958.00    $2,792.00  $4,250.00  $6,542.00    
S    $417.00      $625.00    $1,042.00    $1,875.00  $3,333.00  $5,625.00    
Z    $0.00     $208.00    $625.00    $1,456.00  $2,917.00  $5,208.00

hello guys,
query help, my problem is how to query from this table where Code="HF" and the value is 1000, i want to select form range1 up to range and get the nearest value which is not more than 1000.. Regards

thanks in advance

[QVeen72]

Hi,

Open a RecordSet For Code ='HF' and put in a loop and Check, Something like This:

Dim RST As RecordSet
Dim FName As String
Dim MyQty As Currency
Dim sSQL As String
Dim i As Integer
 
sSQL ="Select * From MyTable Where Code='HF'"
Set RST =DB.OpenRecordSet(sSQL,dbOpenSnapShot)
IF RST.RecordCount>0 Then
  MyQty = RST(1)
  For i = 1 To RSt.Fields.Count-1
     FName = "Range" & i
     If RST(FName) > 1000 Then
        Exit For
     End If
     MyQty = RST(FName)
  Next
End If
 
MsgBox MyQty

Another way is to Open a RecordSet With "UNION ALL" condition.. but that will not be feasible in such Cases..

REgards
Veena

[royaloba]

hello Venna,

rs_Wtax.CursorLocation = adUseClient
    rs_Wtax.Open "Select * from tblWTax where Code='" & "HF" & "'", conn
    If rs_Wtax.RecordCount > 0 Then
        MyQty = rs_Wtax(1)
        For i = 1 To rs_Wtax.Fields.Count - 1
            FName = "Range" & i
            If rs_Wtax(FName) > Val(Text1) Then
                Exit For
            End If
        MyQty = rs_Wtax(FName)
        Next
    End If
    MsgBox MyQty
    Set rs_Wtax = Nothing

it works very nice.. thanks