trying to capture numbers in a text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

but when I use the "Or" statement, the data is no longer passed to my query.

Any suggestions. I'm not really a programmer so simple is key.
 
Hi, Milez.

Hard to tell what you're trying to do. Please post additional detail,
including cutting and pasting your Or statement, and we'll try to help.

Sprinks
 
I have order number like 13400

I need users to input up to 20 of them...

I'm using the order numbers as criteria in a query. Currently, I am using 20
separate text boxes, and twenty "or" statements in the query. One for each
text box. This works well enough, but I can only fit 250 characters in the
Criteria box of the query, and so 20 is the limit.

I'd like to have the users type "Or" between the invoice numbers in a single
text box, but for some reason it stops the data from passing into the query.
 
Hi, Milez.

Add another textbox. For now leave it Visible while you get the code below
working. After you do, you can make it invisible. For now, I've assumed
that every textbox on the form is one of your order number input boxes.

Press <Ctrl-G> from from design view to open VBA. Cut and paste the code
below into the Module window, changing your formname and control name as
necessary. Save and exit.

Private Sub BuildControlString()
Dim ctl As Control
Dim strCriteria As String

strCriteria = ""
Forms!YourFormName!YourAddedTextboxControlName

' Loop through Form's Controls collection, building your criteria string
For Each ctl in Me.Controls
If (ctl.ControlType = acTextbox AND Not IsNull(ctl) )Then
strCriteria = strCriteria & ctl.Value & " OR "
End If
Next ctl

' Strip off last " OR "
strCriteria = Left(strCriteria,len(strCriteria)-4)

' Assign variable to hidden textbox
Forms!YourFormName!YourAddedTextboxControlName = strCriteria

End Sub

If you're using a command button to execute the query, insert the following
line at the top of the OnClick procedure:

Call BuildControlString

Then, in your query, set the Criteria to be equal to the hidden textbox
control.

Hope that helps.
Sprinks
 
Back
Top