Numerous entries in one text box

[arage]

Numerous entries in one text box
Hi I can’t find my previous post regarding this so am forced to ask again.

I have a text box.

I want to enter several numbers in it like so 1,2,3,4,5….

How can I do this so that when the query runs it looks at this control, figures out what the values are & returns them correctly. Someone mentioned something about Instr() before but I can’t find the post.

*Another way to state the problem is that “Each comma delimited value in a text box should stand for a parameter to a query.”


[This message has been edited by arage (edited 01-22-2002).]

 

[Alexandre]

To me, the real problem is rather: how do you ensure/check wether th expression entered by the user is valid?
- right syntax 1,2,3 not 1,,2,
- using existing criteria (not a,1,2)

if the string entered in your textbox is correct, then you can simply use it as part as a INstatement in the Where clause of your query:

ex:
OnOpen event of the report:

strMyWhereClause= "[MyField] IN (" & Forms!MyForm![MyTextBox] & ")"
ApplyFilter , strMyWhereClause

Alex

[This message has been edited by Alexandre (edited 01-22-2002).]

 

[arage]

Well it sort of goes back to my original problem b/c my WHERE clause will need to include values that may or may not exist in 5 text boxes.

My report currently has a macro run on its onOpen to do an applyFilter by record type. Now I added 5 more filters in it like so to account for values in the text boxes….

[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent0])
[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent1])
[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent2])
[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent3])
[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent4])
[NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent5])

This method isn’t working though.

Btw, I’m currently the only user on this form.

 

[Alexandre]

Ok, I will make a few assumptions and suppose that you want to filter on records matching

([NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent0])
OR
([NewQuery]![EventNumber] In ([Forms]![frmTest]![txtEvent1])
OR...

Then, for example:

Option Compare Database
Option Explicit


Function WHERECLAUSE()
Dim strMyWhereClause, strTxtBox As String
Dim i As Integer


strMyWhereClause = "([EventNumber] IN (" & [Forms]![frmTest]![txtEvent0] & "))"
For i = 1 To 5
'Skip if empty
If Len(Nz(Forms.Item("frmTest").Controls("txtEvent" & i), "")) <> 0 Then
    strMyWhereClause = strMyWhereClause & " OR ([EventNumber] IN (" & Forms.Item("frmTest").Controls("txtEvent" & i) & "))"
End If
Next i


Debug.Print strMyWhereClause


End Function

Hope this helps.

Alex

[This message has been edited by Alexandre (edited 01-22-2002).]

 

[arage]

Works yes and no. Currently if I enter 1 pair of controls, I get results for all 5 regions rather than just the one I selected.

My filtering is like so for the report that is output..
-By type (main form control)
-By region (tab control combo box – 5 pairs)
-By event (tab control text box – 5 pairs)

 

[Alexandre]

Provide a concrete example to help understand how your criteria must be combined.

(Give a value or null to each control and tell what this combination should mean in terms of filtering criteria).

ALex

 

[arage]

hehe…you’re a saint if I ever met one Alex….hehe…
like I said I described my form above in my last response…
the form also has a button that will run a report…
the report is based upon a query….

1.User clicks main form drop down and chooses type OA or BE (for the type of output records to be displayed)

2.User goes to tab control & selects drop down box and chooses a region (your options are 1100,1200….to 1500) There are 5 of these controls on the tab, one for each region.

3.User then goes to a text box next to the combo box in step 2 & enters 1 or more event numbers, comma delimited ie (1,2,3,4…) There are 5 of these controls on the tab, one text box for every region mentioned in step 2.

4. do step 2 & 3 if you need to enter info for more regions.

5.Click goButton & run report.

The report will only run if step 1 is done as well as step 2 and 3 (for 1 or more regions)
Hope this helps!

 

[Alexandre]

I do not think that there is another alternative than programatically building the Where clause.
The following example assumes that Region/Events pars must only be used as criteria if both the Region and the Event where filled in for the same par. Some further string processing wil be needed otherwise.

Option Compare Database
Option Explicit


Function WHERECLAUSE() As String


Dim frmMyForm As Form
Dim strMyWhereClause, strControl1, strControl2 As String
Dim bCriteriaExists As Boolean
Dim i As Integer


bCriteriaExists = False
strMyWhereClause = ""
Set frmMyForm = Forms.Item("frmTest")
strControl1 = frmMyForm.Controls("cbType")


If Len(Nz(strControl1, "")) <> 0 Then
    strMyWhereClause = "([Type] = " & strControl1 & ") AND ("
    For i = 0 To 5
        strControl1 = Nz(frmMyForm.Controls("txtEvent" & i), "")
        strControl2 = Nz(frmMyForm.Controls("cbRegion" & i), "")
        If Not (Len(strControl1) = 0 Or Len(strControl2) = 0) Then
            bCriteriaExists = True
            strMyWhereClause = strMyWhereClause & " OR ([Region] = " & strControl2 & _
                                " AND [EventNumber] IN (" & strControl1 & "))"
        End If
    Next i


'Trim the extra " OR " before the first Region/Events criteria and put a final ")"
    If bCriteriaExists = True Then
        strMyWhereClause = Left$(strMyWhereClause, InStr(1, strMyWhereClause, " OR ") - 1) & _
                            Right$(strMyWhereClause, Len(strMyWhereClause) - InStr(1, strMyWhereClause, " OR ") - 3) & ")"
    Else:
        strMyWhereClause = ""
    End If


    WHERECLAUSE = strMyWhereClause


End If


End Function

[This message has been edited by Alexandre (edited 01-23-2002).]

 

[arage]

Thanks for the code Alex still not working though.

I get prompted for 3 query parms when I run report, for TYPE, OA, & REGION.
The second parm I don’t understand, I think it’s referring to the drop down on the main form. And when I fill these parms I’ll get events for all REGIONS regardless. The events are working correctly if I enter them comma delimited.

The results seemingly returned are all related to the FIRST PAIR of controls on the tab control though.

 

[Alexandre]

Problem with AO was due to missing quotes that must surround a string type value in the WHERE clause(while not needed for numbers). The rest DOES work provided that -of course- you do the effort to adapt the field and controls names to your concrete case (Hence the other required parameter messages that you get).

I quickly set up a demo DB that I can send you if you provide an Email adress.

Alex

[This message has been edited by Alexandre (edited 01-23-2002).]

 

[arage]

Well I believe I got around the string problem with this:
---
strMyWhereClause = "([NewQuery]![PromotionType] = '" & strControl1 & "') AND ("
---

Right now for anything I do, I essentially get all records returned.

Another point is that in debug mode I check out the value of WHERECLAUSE & it always contains the correct WHERE clause.

If I place the string in WHERECLAUSE in some other queries SQL I get the correct results, but in the report I am opening with the openReport method, the incorrect stuff comes up because the WHERE CLAUSE is somehow not being applied.

 

[Alexandre]

On click event of the Command button used to launch the report from your form:

Private Sub cmdValidate_Click()


'Debug.Print WHERECLAUSE


If WHERECLAUSE = "" Then Exit Sub 'Or display a message saying that criteria were not filled properly


DoCmd.OpenReport "ReportName", acViewPreview, , WHERECLAUSE
End Sub

Alex

[This message has been edited by Alexandre (edited 01-23-2002).]

 

[arage]

ok, I’m really scared now b/c it seems to be working perfectly.

This is what I had originally in my button’s onClick event.

'DoCmd.OpenReport "rptSearchByRefNum", acViewPreview, WHERECLAUSE

and this is Alexs’:

DoCmd.OpenReport "rptSearchByRefNum", acViewPreview, , WHERECLAUSE

Are you telling me that the exclusion of a single comma (filterName arguent) is what was messing me up?

 

[Alexandre]

Of course.
Functions parameters may be optional (not required) or/and default to a pre-defined value if not provided by the user. But each parameter must be entered (or ignored) respecting the predefine sequence (How would Access guess what parameter you are filling in otherwise?)
Access helps you for that, showing parameters names while you write the function. Currently filled parmeter is bolded. Brackets indicate optional parameters, = x indicates that the parameter defaults to x if ignored.
If you want to ignore a parameter but fill in the following, you must indicate that you pass to the next one but writing a comma.

Alex

[This message has been edited by Alexandre (edited 01-23-2002).]

 

[arage]

i officially close this thread.

many thanks Alex!