Text Box Value into a query

[mavver]

hello all

I have a form, which has a number of multiselect list boxes on it.

I have managed to work through the code to create an SQL string so that it queries a table based on these selections (something that I am very proud of)

The basic jist of that code is

If Me!lstSchoolPoE.ItemsSelected.Count > 0 Then

For Each varItemPoE In Me!lstSchoolPoE.ItemsSelected
strCriteriaPoE = strCriteriaPoE & ",'" & Me!lstSchoolPoE.ItemData varItemPoE) & "'"
    Next varItemPoE
strCriteriaPoE = Right(strCriteriaPoE, Len(strCriteriaPoE) - 1)
strCriteriaPoE = "tblSchool_Clean.PoE_Desc IN(" & strCriteriaPoE & ")"
Else
strCriteriaPoE = "tblSchool_Clean.PoE_Desc Like '*'"
End If

This then goes on for three list boxes and finishes with

strSQL = "SELECT * FROM tblSchool_Clean " & _
"WHERE " & strCriteriaPoE & _
"AND " & strCriteriaToE & _
"AND " & strCriteriaLocation & ";"

However, I am now struggling on what appears on the surface to be an easier issue.

I want to add the option for the user to query some numeric fields in the table at the same time, thus by saying the following

strSQL = "SELECT * FROM tblSchool_Clean " & _
"WHERE  tblSchool_Clean.TOTAL_RESIDENTS > 50 ;"

This works fine, however, I would like the user to be able to enter the number on the form, via a text box. I tried the following and it didnt work

strSQL = "SELECT * FROM tblSchool_Clean " & _
"WHERE  tblSchool_Clean.TOTAL_RESIDENTS > me.txtResidents ;"

So I suppose, the easier way of saying this, is how do I link a text box, with a number in it, from a form into a SQL statement?

Thanks for the help

Mav

 

[neileg]

'Me' refers to the current object, which is your query, so Access can't find the text box. You need to tell it where to look

WHERE  tblSchool_Clean.TOTAL_RESIDENTS > Forms!YourFormNameHere!txtResidents ;

 

[mavver]

Ta for that

Just one more bit

How would you tell access to look at another text box at the same time to get min AND max parameters

WHERE tblSchool_Clean.TOTAL_RESIDENTS > Forms!YourFormNameHere!txtResidents1 & _
AND tblSchool_Clean.TOTAL_RESIDENTS < Forms!YourFormNameHere!txtResidents2 ;

would that work?

ta again

 

[ianward]

I think you could use a Between in your statement as follows

WHERE  tblSchool_Clean.TOTAL_RESIDENTS 
Between Forms!YourFormNameHere!txtResidentsMax
AND Forms!YourFormNameHere!txtResidentsMin ;

Ian

 

[neileg]

Yeah, what he said!