-
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?
------------------
-
You can't place a reference to your VB Variable(s) directly in the SQL String, instead you need to add their values to the string, eg.
Code:
txtPressIn = 1150
txtTempIn = 900
txtSizeIn = 16
strSQL = "SELECT ModelNumber FROM _
tblPressureData WHERE MaxPressure >= " & txtPressIn & _
" ORDER BY MaxPressure"
Assuming MaxPressure is of Numeric Data Type in the DB, Character Datatypes should be enclosed in Single or Double Quotes.
------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]
