Using Variable as criteria

[Mr. Bud]

Hi and TIA. I have a procedure that constructs an In statement and stores
that value in a module level variable. I then want to use criteria in my
query to call the function to pull the value stored in the variable. I know
that data being stored is constructed as required but when I run the query
with the criteria to GetUIC() no records are returned. I have a control on
my form that calls the GetUIC() function and displays the string that was
built from my multi-select listbox. If I cut and paste that string as the
criteria for my query the correct records are returned. Procedure below
creates the string. The field is a text field. Any ideas are appreciated.

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
Dim strControl As String

strControl = "lstUIC"

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
SetUIC (strWhere) 'Store value to module level variable
Me.Recalc
Debug.Print strWhere
End Function

Debug window returns = IN ('68971', '22202', '21533') and if I place this as
the criteria the records are returned. Thanks for your time.

 

[Duane Hookom]

You can't use any variable or function like this in a criteria. You can
build a where condition string to use in DoCmd.OpenForm/OpenReport. You can
also use a little DAO code to change the SQL property of a saved query.

 

[John W. Vinson]

End Select
SetUIC (strWhere) 'Store value to module level variable
Me.Recalc
Debug.Print strWhere
End Function

What are you doing with strWhere, or with the module level variable? Are you
at some point setting the Form's Filter or Recordsource to that string? If not
the variable won't affect the screen display.

 

[Mr. Bud]

What are you doing with strWhere, or with the module level variable? Are
you
at some point setting the Form's Filter or Recordsource to that string? If
not
the variable won't affect the screen display. Going by what Duane said I
cannot use the variable like the way I am wanting to do which is build the
where statement, store it to my module level variable, then as the
criteria for my site field call the GetUIC() function to pull the
"IN('032396','23456')"call a funtion> --

John W. Vinson [MVP]

John, I am using strWhere to set the module level variable to be used as the
where clause for my query(s). This string will be built from my
multi-select listbox on the form that contains all my military sites.I then
have a procedure that creates an excel workbook and adds a new worksheet to
it for each individual site. Think I will do as Duane said and simply
change my SQL property then use that query to pull records from my database.
Thanks much for your time!

 

[Mr. Bud]

You can't use any variable or function like this in a criteria. You can
build a where condition string to use in DoCmd.OpenForm/OpenReport. You
can also use a little DAO code to change the SQL property of a saved
query.

--
Duane Hookom
MS Access MVP

Hi and TIA. I have a procedure that constructs an In statement and
stores that value in a module level variable. I then want to use
criteria in my query to call the function to pull the value stored in the
variable. I know that data being stored is constructed as required but
when I run the query with the criteria to GetUIC() no records are
returned. I have a control on my form that calls the GetUIC() function
and displays the string that was built from my multi-select listbox. If
I cut and paste that string as the criteria for my query the correct
records are returned. Procedure below creates the string. The field is a
text field. Any ideas are appreciated.

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
Dim strControl As String

strControl = "lstUIC"

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
SetUIC (strWhere) 'Store value to module level variable
Me.Recalc
Debug.Print strWhere
End Function

Debug window returns = IN ('68971', '22202', '21533') and if I place this
as the criteria the records are returned. Thanks for your time.

Duane, Thanks for the reply. That's what I will try. I will change the
query SQL, save it, and use that query to pull records from my tables.