Using text in a text box in SQL select statement

[tomnott]

Hi All,

I'm so heavily frustrated by this puzzle. Normally, i find answers on these forums so quickly and easily, or at least ones i can amend to suit my needs but this has really got me.

I have a text box on my form, and what i'd ideally like to do, is to use the value in that text box (which is a number) to select the top n (corresponding to the value in the text box) value in a query.

Is there anyway of doing this? Or any route around it that will give me similar results that others have come up with?

Any help would be greatly greatly appreciated.

Thanks

 

[dhookom]

You could write some DAO code that would modify the SQL of a saved query. Another option would be to add a ranking column in the query and set the criteria under the ranking column to <= the value from the text box.

Duane
Hook'D on Access
MS Access MVP

 

[tomnott]

Thanks for the rapid response!

I dont know DAO code (is it easy to pick up? because it sounds pretty useful?) but your other option has intrigued me. I ideally need a percentage but that's easy enough to sort out using another invisible text or with a formula here and there. Thanks for the idea!

Much appreciated.

 

[tomnott]

I've been trying to do ranking column but can't work it out. Numbering the rows isn't as easy as i'd imagined either. Do you know a good way of doing this?

My table name is qry_random_all_details, with the unique (although not primary key'd) field name is repair_number. There are about 350 rows, and the issue i'm having is that they're a random selection from a much larger list (looking for sampling analysis).

Thanks again in advance

Tom

 

[MajP]

http://allenbrowne.com/ranking.html

This shows an example. Look at the ranking customers by value query.

 

[kjv1611]

For the SQL in VBA portion, it'd probably have helped if you posted your actual code you were trying to use.

Here's how I would reference the value in a form textbox using SQL. You said it's a numeric value, so I'll keep post specifically for numeric values:

Private Sub RunSomeSQL()
  Dim strSQL as String
  strSQL = "SELECT MyTable.* FROM MyTable WHERE MyTable.Weight > " & txtMyTextBox
  DoCmd.RunSQL strSQL
  strSQL = vbNullString
End Sub

Anyway, that's a silly example, but hopefully it'll get you where you wanting to be.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

The DAO code would look something like:

   Currentdb.QueryDefs("qry_random_all_details").SQL = _
    "SELECT TOP 3 * FROM [Somewhere] ORDER BY [SomeField];"

Duane
Hook'D on Access
MS Access MVP

 

[tomnott]

Thank you all for your help!

dhookom, that bit of code you've given me has done the trick! It's done exactly what i wanted and I can even put a text box reference in place of the '3'.

You're a legend.

Thanks again.

 

[dhookom]

Sorry about not putting a reference to a control on a form in place of the 3. Glad you got it sorted out.

Duane
Hook'D on Access
MS Access MVP