Searching multiple fields in a database

[johnpc]

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?

[JHausmann]

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).]