multiple variables

[Marc]

Hello,

I have a query that runs off a form. The users type in a Customer ID in a
text box in the form, plus a date range before running the query. They've
asked me if they can put in multiple Customer IDs, similar to when you put
multiple choices in the "criteria" line of the query.

Is this possible?

Thanks,

 

[George Nicholson]

you can use
IN('Cust1','Cust2','Cust3')
on a single criteria line of a query rather than using multiple lines, so
yes, I'd say it was possible, but i don't know enough about how your
"...query that runs off a form..." to tell you exactly how to do it in your
case.

HTH,

 

[Marc]

I don't want the user to enter info in the query, I want them to be able to
use the form. Right now, there is a control in the form, "customerID". The
query also has the "customerID" field with the criteria "WHERE
(((tblCustomers.CustomerID)=[forms]![frmTest]![CustomerID]). Thus my "runs
off a form" comment. What I want to be able to do is enter more than one
CustomerID in the control in the form.
I don't want to enter the data in the criteria line of the query.


Thanks,

 

[Klatuu]

Yes, but it take some advanced VBA coding.
First, you need to use a Multi Select List box to list all the customers.
When you initiate the query, you need to do several things. First, you need
to create a string of selected customers formated so the query can use it.
Here is a function for that:
****************************
Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
*****************

Next, you will need to modify the stored query or create your query in VBA.
My prefered technique is to create a stored query that has no Where clause
and save it with a z prefilx (zqselCustList for example) And save a copy of
it without the z

Now we need to actually modify the query with the conditions selected by the
user.

Private Sub cmdDoQuery_Click()
Dim strSQL As String
Dim qdf As QueryDef
Dim strCustList As String
Dim strDateRange As String
Dim strWhere As String

'Get the list of selected customers
strCustList = BuildWhere("MyListBoxName")

'Get the start and end dates
If Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDAte) Then
strDateRange = "BETWEEN #" & Me.txtStartDate & "# AND #" & _
Me.txtEndDate & "#"
ElseIf Not IsNull(Me.txtStartDate) Then
strDateRange = ">= #" & Me.txtStartDate & "#"
ElseIf Not IsNull(Me.txtEndDate) Then
strDateRange = "<= #" & Me.txtEndDate & "#"
End If

'Get the template query
strSQL = currentdb.querydefs("zqselCustList").SQL

'Add Customers if any selected. If none selected, all included
If Len(strCustList) > 0 Then
strWhere = strWhere & strCustList
End If
'Add Date Range if any selected. If none selected, all included
If Len(strDateRange) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & strDate Range
End If
'See if any criteria selected
If Len(strWhere) > 0 Then
strWhere = "WHERE " & strWhere & ";"
End If

'Update the query with the criteria
strSQL = Replace(strSQL, ";", strWhere)
CurrentDb.Querydefs("qselCustList").SQL = strSQL

Now the query is ready to run. You did not say what kind of query, so I
don't know where to go from here, but the query qselCustList now reflects the
user's choices.
You may need to modify the Replace function based on what else may be in
your query.

I will not guarantee there are no bugs in the code. It was typed directly
in here, but it is based on similar situations I have done. The BuildWhere
function, however, is fine. I use it a lot.

 

[Marshall Barton]

I have a query that runs off a form. The users type in a Customer ID in a
text box in the form, plus a date range before running the query. They've
asked me if they can put in multiple Customer IDs, similar to when you put
multiple choices in the "criteria" line of the query.

Use a blank field with an expression like:

InStr(Forms!theform.thetextbox, [Customer ID])
and use the criteria >0

It gets messier is one ID can be found as part of another
ID.

 

[Marc]

Thanks Dave,

I'll give it a try.

Thank,

 

[Marc]

Hi Marshall,

I'm not sure I follow what you're saying. Where do I put the blank field?

Thanks,
--
Marc

I have a query that runs off a form. The users type in a Customer ID in a
text box in the form, plus a date range before running the query. They've
asked me if they can put in multiple Customer IDs, similar to when you put
multiple choices in the "criteria" line of the query.

Use a blank field with an expression like:

InStr(Forms!theform.thetextbox, [Customer ID])
and use the criteria >0

It gets messier is one ID can be found as part of another
ID.

 

[Marshall Barton]

I'm not sure I follow what you're saying. Where do I put the blank field?

Let's review my understanding of what you want to do here.
You have a text box (named txtFind) on a form (named
theform) where users enter a list of customer ids, e.g.
a1b, d3x, ww2
The goal is to use a query criteria that will find all
records that contain any of the ids in the list.

In the query's design grid, create a calculated field in an
unused (blank) column:

Field Expr1: InStr(Forms!theform.txtFind, [Customer ID])
Criteria >0

 

[Marc]

Brilliant! It worked. Exactly what I was looking for.

Thanks so much.
--
Marc

I'm not sure I follow what you're saying. Where do I put the blank field?

Let's review my understanding of what you want to do here.
You have a text box (named txtFind) on a form (named
theform) where users enter a list of customer ids, e.g.
a1b, d3x, ww2
The goal is to use a query criteria that will find all
records that contain any of the ids in the list.

In the query's design grid, create a calculated field in an
unused (blank) column:

Field Expr1: InStr(Forms!theform.txtFind, [Customer ID])
Criteria >0

 

[banem2]

Brilliant! It worked. Exactly what I was looking for.

Thanks so much.
--
Marc

Let's review my understanding of what you want to do here.
You have a text box (named txtFind) on a form (named
theform) where users enter a list of customer ids, e.g.
a1b, d3x, ww2
The goal is to use aquerycriteriathat will find all
records that contain any of the ids in the list.

In thequery'sdesign grid, create a calculated field in an
unused (blank) column:

Field Expr1: InStr(Forms!theform.txtFind, [Customer ID])
Criteria >0

What if there is no filter in txtFind? How to show all records and
keep all in one query?

I have tried Like(IIf(IsNull(Forms!theform.txtFind), >0, >=0)) but no
luck.

 

[banem2]

Brilliant! It worked. Exactly what I was looking for.

Thanks so much.

Marc wrote:
I'm not sure I follow what you're saying. Where do I put the blank field?
Let's review my understanding of what you want to do here.
You have a text box (named txtFind) on a form (named
theform) where users enter a list of customer ids, e.g.
a1b, d3x, ww2
The goal is to use aquerycriteriathat will find all
records that contain any of the ids in the list.
In thequery'sdesign grid, create a calculated field in an
unused (blank) column:
Field Expr1: InStr(Forms!theform.txtFind, [Customer ID])
Criteria >0

What if there is no filter in txtFind? How to show all records and
keep all in onequery?

I have tried Like(IIf(IsNull(Forms!theform.txtFind), >0, >=0)) but no
luck.

I must tell something more about my case.

I have on form frame with 3 options to select report type (All, Top
20, Selected) where Selected reveals ListBox with multi-selection.
There are many options selectable and when combined it will produce
around 7,800 different reports. My goal is to use minimum set of
queries and reports and I will cover with only couple of queries and
reports all cases. Therefore high optimization is required.

Using your solution and for selection from ListBox I have in query:

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

"[frame85]=3" indicates that user have selected ListBox option.

Note that I have used workaround - Between 1 and 1000000 or Between 1
and 1000000 and I am not happy with this solution, but at the moment I
don't have any better. Basically this is my question - are there any
better solution?

GetTitles() is sub which determine report type selected and provides
filter to query.

Best Regards,
Branislav Mihaljev

 

[banem2]

I have on form frame with 3 options to select report type (All, Top
20, Selected) where Selected reveals ListBox with multi-selection.
There are many options selectable and when combined it will produce
around 7,800 different reports. My goal is to use minimum set of
queries and reports and I will cover with only couple of queries and
reports all cases. Therefore high optimization is required.

Using your solution and for selection from ListBox I have in query:

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

"[frame85]=3" indicates that user have selected ListBox option.

Note that I have used workaround - Between 1 and 1000000 or Between 1
and 1000000 and I am not happy with this solution, but at the moment I
don't have any better. Basically this is my question - are there any
better solution?

GetTitles() is sub which determine report type selected and provides
filter to query.

As long as you have a button on the form that open the
report, you can use code instead of going through
contortions in the query.

Use the OpenReport method's WhereCondition argument to
filter the data to the items in the list box, something like
this air code:

Select Case Me.frame85
Case 3
stWhere = "NameVO IN(" & GetTitles() & ")"
DoCmd.OpenReport "report name", acViewPreview, _
WhereCondition:= stWhere
Case 1
DoCmd.OpenReport "report name", acViewPreview
Case 2
DoCmd.OpenReport "report name", acViewPreview, _
OpenArgs:= "XXX"
End Select

To deal with case 2, you will need some code in the report's
Open event. For example:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = "your top 20 query name"
End If

You will probably have to tweak the GetTitles function to
use a comma separator and if the NameVO field is a Text
field, add quote delimiters around each item.

Using these techniques to deal with your options, you only
need one copy of the report. You will need two queries,
both of them without any criteria. Just basic Select
queries, one with the TOP 20 and associated ORDER BY clause.

Thanks Marsh,

But I have made query as record source for report already. That is
what GetTitles() sub do too - it determines which option have been
selected and provide to query string like: "Star Wars" OR "Mr. Bean"
OR... and simply filters query for all 3 cases. I am copying that Sub
here hopping that someone will find useful this approach:

Public Sub GetTitles()
'Used to determine which report type is selected
'frame85 is a frame control with 3 options
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strTitles As String
Dim rst As Recordset, qdf As QueryDef

Set frm = Forms!frmReport 'Name of form to define report criteria
Set ctl = frm!lstTitles 'ListBox with multi-selection

If frm!Frame85 = 3 Then 'Get selected titles option selected
For Each varItem In ctl.ItemsSelected 'Loops through ListBox
strTitles = strTitles & ctl.ItemData(varItem) & " OR "
Next varItem
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

ElseIf frm!Frame85 = 2 Then 'Get Top 20 Titles option selected
Set qdf = CurrentDb.QueryDefs("qryTop20Titles")
qdf.Parameters(0) = [Forms]![frmReport]![fldDateFrom] 'Dates
are optional
qdf.Parameters(1) = [Forms]![frmReport]![fldDateTo]
Set rst = qdf.OpenRecordset
With rst
Do While .EOF = False
strTitles = strTitles & !NameVO & " OR " 'NameVO =
Title Name
.MoveNext
Loop
End With
Set rst = Nothing
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

Else 'Get ALL Titles 'Else 3rd option have been selected
GetTitles = "((tblTheatresTitlesAdmissionsShares.IDTitle) >
0)"
End If
End Sub

Basically, as you see, I have used your idea, but the question is this

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

the best solution?

Can I wrote something like

Between (1) and (Unlimited) as Like(IIf..., >0, >-1) won't work?
Actually is there any way to have filter as ">0" or ">-1" (for all)? I
assume "Like" returns string so >-1 is not equal with ">-1".

Couple of notes: Select Case is better approach but when I have
started I have had 2 options only. Second, I have left "frame85" there
instead something more meaningful so it looks obvious that I have used
frame. Last, qryTop20 provides list of Top 20 Titles by Gross BO.

Finally, single query solution covers couple of hundreds of different
reports as there are more filtered fields in query like the one above
and it works really quick.

Best regards,
Branislav Mihaljev

 

[Marshall Barton]

To me, it looks like your insistence on putting all this
stuff in a query's criteria is just making the problem more
complicated than using the WhereCondition argument. You
could reduce the number of queries that you need for all
your various criteria to only a couple (a TOP 20 query and a
regular query).

Your Between 0 and 1000000 appears to be nonsense since
InStr returns a 0 if the match is not found and a >0 value
if it is found.

I don't understand why the GetTitles function's cases 2 and
3 is putting " OR " in between each title (a "," will do)
and the case 1 result will not work at all.

However, I think you can get the query criteria to work if
the function's case 1 returns an empty string and you use
the criteria:

= IIf([forms]![frmReport]![frame85]=1,0,1)

--
Marsh
MVP [MS Access]

I have on form frame with 3 options to select report type (All, Top
20, Selected) where Selected reveals ListBox with multi-selection.
There are many options selectable and when combined it will produce
around 7,800 different reports. My goal is to use minimum set of
queries and reports and I will cover with only couple of queries and
reports all cases. Therefore high optimization is required.

Using your solution and for selection from ListBox I have in query:

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

"[frame85]=3" indicates that user have selected ListBox option.

Note that I have used workaround - Between 1 and 1000000 or Between 1
and 1000000 and I am not happy with this solution, but at the moment I
don't have any better. Basically this is my question - are there any
better solution?

GetTitles() is sub which determine report type selected and provides
filter to query.

As long as you have a button on the form that open the
report, you can use code instead of going through
contortions in the query.

Use the OpenReport method's WhereCondition argument to
filter the data to the items in the list box, something like
this air code:

Select Case Me.frame85
Case 3
stWhere = "NameVO IN(" & GetTitles() & ")"
DoCmd.OpenReport "report name", acViewPreview, _
WhereCondition:= stWhere
Case 1
DoCmd.OpenReport "report name", acViewPreview
Case 2
DoCmd.OpenReport "report name", acViewPreview, _
OpenArgs:= "XXX"
End Select

To deal with case 2, you will need some code in the report's
Open event. For example:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = "your top 20 query name"
End If

You will probably have to tweak the GetTitles function to
use a comma separator and if the NameVO field is a Text
field, add quote delimiters around each item.

Using these techniques to deal with your options, you only
need one copy of the report. You will need two queries,
both of them without any criteria. Just basic Select
queries, one with the TOP 20 and associated ORDER BY clause.

But I have made query as record source for report already. That is
what GetTitles() sub do too - it determines which option have been
selected and provide to query string like: "Star Wars" OR "Mr. Bean"
OR... and simply filters query for all 3 cases. I am copying that Sub
here hopping that someone will find useful this approach:

Public Sub GetTitles()
'Used to determine which report type is selected
'frame85 is a frame control with 3 options
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strTitles As String
Dim rst As Recordset, qdf As QueryDef

Set frm = Forms!frmReport 'Name of form to define report criteria
Set ctl = frm!lstTitles 'ListBox with multi-selection

If frm!Frame85 = 3 Then 'Get selected titles option selected
For Each varItem In ctl.ItemsSelected 'Loops through ListBox
strTitles = strTitles & ctl.ItemData(varItem) & " OR "
Next varItem
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

ElseIf frm!Frame85 = 2 Then 'Get Top 20 Titles option selected
Set qdf = CurrentDb.QueryDefs("qryTop20Titles")
qdf.Parameters(0) = [Forms]![frmReport]![fldDateFrom] 'Dates
are optional
qdf.Parameters(1) = [Forms]![frmReport]![fldDateTo]
Set rst = qdf.OpenRecordset
With rst
Do While .EOF = False
strTitles = strTitles & !NameVO & " OR " 'NameVO =
Title Name
.MoveNext
Loop
End With
Set rst = Nothing
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

Else 'Get ALL Titles 'Else 3rd option have been selected
GetTitles = "((tblTheatresTitlesAdmissionsShares.IDTitle) >
0)"
End If
End Sub

Basically, as you see, I have used your idea, but the question is this

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

the best solution?

 

[banem2]

To me, it looks like your insistence on putting all this
stuff in a query's criteria is just making the problem more
complicated than using the WhereCondition argument. You
could reduce the number of queries that you need for all
your various criteria to only a couple (a TOP 20 query and a
regular query).

Your Between 0 and 1000000 appears to be nonsense since
InStr returns a 0 if the match is not found and a >0 value
if it is found.

I don't understand why the GetTitles function's cases 2 and
3 is putting " OR " in between each title (a "," will do)
and the case 1 result will not work at all.

However, I think you can get the query criteria to work if
the function's case 1 returns an empty string and you use
the criteria:

= IIf([forms]![frmReport]![frame85]=1,0,1)

--
Marsh
MVP [MS Access]

[email protected] wrote:
I have on form frame with 3 options to select report type (All, Top
20, Selected) where Selected reveals ListBox with multi-selection.
There are many options selectable and when combined it will produce
around 7,800 different reports. My goal is to use minimum set of
queries and reports and I will cover with only couple of queries and
reports all cases. Therefore high optimization is required.
Using your solution and for selection from ListBox I have in query:
Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))
"[frame85]=3" indicates that user have selected ListBox option.
Note that I have used workaround - Between 1 and 1000000 or Between 1
and 1000000 and I am not happy with this solution, but at the moment I
don't have any better. Basically this is my question - are there any
better solution?
GetTitles() is sub which determine report type selected and provides
filter to query.
As long as you have a button on the form that open the
report, you can use code instead of going through
contortions in the query.
Use the OpenReport method's WhereCondition argument to
filter the data to the items in the list box, something like
this air code:
Select Case Me.frame85
Case 3
stWhere = "NameVO IN(" & GetTitles() & ")"
DoCmd.OpenReport "report name", acViewPreview, _
WhereCondition:= stWhere
Case 1
DoCmd.OpenReport "report name", acViewPreview
Case 2
DoCmd.OpenReport "report name", acViewPreview, _
OpenArgs:= "XXX"
End Select
To deal with case 2, you will need some code in the report's
Open event. For example:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = "your top 20 query name"
End If
You will probably have to tweak the GetTitles function to
use a comma separator and if the NameVO field is a Text
field, add quote delimiters around each item.
Using these techniques to deal with your options, you only
need one copy of the report. You will need two queries,
both of them without any criteria. Just basic Select
queries, one with the TOP 20 and associated ORDER BY clause.

But I have made query as record source for report already. That is
what GetTitles() sub do too - it determines which option have been
selected and provide to query string like: "Star Wars" OR "Mr. Bean"
OR... and simply filters query for all 3 cases. I am copying that Sub
here hopping that someone will find useful this approach:

Public Sub GetTitles()
'Used to determine which report type is selected
'frame85 is a frame control with 3 options
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strTitles As String
Dim rst As Recordset, qdf As QueryDef

Set frm = Forms!frmReport 'Name of form to define report criteria
Set ctl = frm!lstTitles 'ListBox with multi-selection

If frm!Frame85 = 3 Then 'Get selected titles option selected
For Each varItem In ctl.ItemsSelected 'Loops through ListBox
strTitles = strTitles & ctl.ItemData(varItem) & " OR "
Next varItem
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

ElseIf frm!Frame85 = 2 Then 'Get Top 20 Titles option selected
Set qdf = CurrentDb.QueryDefs("qryTop20Titles")
qdf.Parameters(0) = [Forms]![frmReport]![fldDateFrom] 'Dates
are optional
qdf.Parameters(1) = [Forms]![frmReport]![fldDateTo]
Set rst = qdf.OpenRecordset
With rst
Do While .EOF = False
strTitles = strTitles & !NameVO & " OR " 'NameVO =
Title Name
.MoveNext
Loop
End With
Set rst = Nothing
GetTitles = Left(strTitles, Len(strTitles) - 4) 'Removes last
" OR " characters

Else 'Get ALL Titles 'Else 3rd option have been selected
GetTitles = "((tblTheatresTitlesAdmissionsShares.IDTitle) >
0)"
End If
End Sub

Basically, as you see, I have used your idea, but the question is this

Expr1: InStr(GetTitles(),[tblTitles].[NameVO])
Criteria: Between (IIf([forms]![frmReport]![frame85]=3,1,0)) And
(IIf([forms]![frmReport]![frame85]=3,1000000,1000000))

the best solution?

Hi Marshall,

Yes - I later realized that " OR " string is not required - I thought
I could reuse function, but "," works too.

I agree that Between 0 and 100000 is nonsense, but I didn't find a way
to set filter. It appears that your advice works.

Thanks!