-
Using VB6 ADO Connection Object with a MS Access database with one Table, tblPressureData containing four Fields;
ModelNumber
MaxPressure
MaxTemperature
MaxSize
Simple listing of the database:
ModelNumber MaxPressure MaxTemp MaxSize
Model1 900 400 36
Model2 1750 1100 10
Model3 400 150 4
Model4 1200 850 14
Is it possible to search through the Table based on a given input criteria loaded via TextBoxes, say txtPressIn; txtTempIn and txtSizeIn to find items that are less than or equal to the values input to these Text Boxes?
Example:
txtPressIn = 1150
txtTempIn = 900
txtSizeIn = 16
Given the above inputs the search results would find only Model4.
I am using the following query;
strSQL = " SELECT ModelNumber FROM _
tblPressureData WHERE MaxPressure >=PressIn _
ORDER BY MaxPressure"
and then;
With oRS
.MoveFirst
.Find "MaxPressure = '" & txtPressureIn & "'"
Do While Not .EOF
If Val(!MaxPressure) >= Val(txtPressureIn) And _
Val(!TempIn) >= Val(txtTempIn) And _
Val(!MaxSize) >= Val(txtSizeIn) Then
Debug.Print !ModelNumber; !MaxPressure; MaxXize
'fill a List Box with acceptable items
List1.AddItem !ModelNumber & " " & !MaxPressure _ & " " & MaxXize
End If
.MoveNext
Loop
.Close
End With
This doesn't quite work. Any suggestions?
-
1) get rid of the if conditional you currently have, so the code looks like:
Do While Not .EOF
Debug.Print !ModelNumber; !MaxPressure; MaxSize
'fill a List Box with acceptable items
List1.AddItem !ModelNumber & " " & !MaxPressure _ & " " & MaxXize
End If
.MoveNext
Loop
2) change the SQL statement to be:
strSQL = " SELECT ModelNumber, MaxPressure, MaxSize FROM tblPressureData WHERE MaxPressure >=" & CLng(form.txtPressureIn) & " and MaxTemp >="
& CLng(form.txtTempIn) & " and MaxSize >=" &
CLng(form.txtSizeIn) & " ORDER BY MaxPressure"
[This message has been edited by JHausmann (edited 11-02-1999).]
