If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Variable as criteria
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. -- Reggie |
#2
|
|||
|
|||
Using Variable as criteria
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 "Mr. Bud" wrote in message ... 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. -- Reggie |
#3
|
|||
|
|||
Using Variable as criteria
On Tue, 4 May 2010 17:47:22 -0700, "Mr. Bud"
wrote: 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. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Using Variable as criteria
"John W. Vinson" wrote in message
... On Tue, 4 May 2010 17:47:22 -0700, "Mr. Bud" wrote: 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. 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! -- Reggie |
#5
|
|||
|
|||
Using Variable as criteria
"Duane Hookom" wrote in message
... 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 "Mr. Bud" wrote in message ... 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. -- Reggie 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. -- Reggie |
Thread Tools | |
Display Modes | |
|
|