For Aaron Young

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



------------------

[Aaron Young]

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.

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
aarony@redwingsoftware.com
adyoung@win.bright.net