Passing global variable to query

[Purdue2479]

I am trying to pass a global variable to criteria in a query using the below code, but the query is returning no results. I have the function ReturnStrCriteria() included in the query's criteria. When I manually put the criteria into the query (Like "*2007"), it returns results. Not sure what I'm doing wrong. Any suggestions would be appreciated. Thanks

Option Compare Database
Option Explicit

  Public Sub cmdOK_Click()

' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strSQL As String

' Get the database and stored query
    Set db = CurrentDb()
    
    DoCmd.RunSQL "Delete * From Table;"
    
    ' Loop through the selected items in the list box and build a text string
    If Me!lstRebate_Period.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstRebate_Period.ItemsSelected
            strCriteria = strCriteria & "Like " & Chr(34) _
                          & "*" & Me!lstRebate_Period.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "Tbl_Payment_YTD.[Rebate Period] Like '*'"
    End If
    
' Open the query
    DoCmd.OpenQuery "Query"

Set db = Nothing

End Sub

Module:

Option Compare Database
Option Explicit

Public strCriteria As String

Public Function ReturnStrCriteria() As String

ReturnStrCriteria = strCriteria

End Function

 

[gemma-the-husky]

i have never tried to build a string like this in a function, just used a function to return a particular value but

first things first


if you display strcriteria before

' Open the query
DoCmd.OpenQuery "Query"

does it look right ie does it look exactly like '(Like "*2007")'

or even '(Like "*2007") OR (Like "*2008")'

--------
it does seem slightly odd using strings to test numerics - i would have thought the criterai ought to look something like just
"2007 or 2008", rather than as strings including like. does the actual period number include a month also?

 

[Purdue2479]

When I add a watch to strCriteria it appears as "Like "*2007""

Yes, the Rebate_Period field also includes the month (i.e. January_2007)


-----

I tried using the parentheses and singles quotes, but I am still unable to get the query to return results. Is this not possible using a function?

 

[Purdue2479]

I can't even get the below to work when just running the query manually using ReturnStrCriteria() as the criteria.

Function ReturnStrCriteria() As String
strCriteria = "Like *"
ReturnStrCriteria = strCriteria
 
End Function

 

[gemma-the-husky]

i've had a play with this - instead of doing it ther way you have been, a different way is to have an array of searchstrings (eg "2007", "2008")

then in your query have a new column

needthis(periodnumber)

with criteria set to true

and then use code in a module something like the following

i've tested this and it works fine

public myarray(10) As String

Sub setarray()
{example code to intiiallize the array}
    myarray(1) = "2007"
    myarray(2) = "2008"
End Sub

Public Function needthis(inname As String) As Boolean
{could be improved, but this tests inname value against the array to determine whether to include this item}

{see above - make sure array is intiallised first}

Dim x As Long
Dim foundit As Boolean

foundit = False
For x = 0 To 10
    If Len(Nz(myarray(x), vbNullString)) > 0 Then
        If InStr(1, inname, myarray(x)) > 0 Then foundit = True
    End If
Next x
needthis = foundit
End Function

 

[JoeyY]

How about taking the 'Like' expression part out as part of your variable strCriteria, and put it in your query criteria?

Like "*" & ReturnStrCriteria()

I think the problem is getting the query to interpret the 'Like' expression when it's passed as a variable.

 

[DCrake]

Simple Software Solutions

Have a look at the attached doc for a detailed explanation of using global variables in queries.

CodeMaster:

 

[gemma-the-husky]

its not that DC

what hes trying to do is build a string in code such as

like "*smith" or like "*jones" and pass THAT as a parameter to the query, so he can allow for a varying number of selections

my solution is tested and works - joeyy's may work as well - but not sure whether he tested it,

 

[Purdue2479]

JoeyY,

That worked like a charm! Thanks!!! I've been stuck on this for over a day now. Thanks to everyone else that also responded.

*Just noticed that it will not work when making multiple selections in the list box. :-(

 

[JoeyY]

Sorry, didn't think it all the way out. I'm still wondering why what you were originally attempting won't work.

I like Gemma's needthis() function, although I had a different idea for setting the myarray variable. Using Gemma's needthis function in the query, what about replacing the cmdOK code with this?

'Reset the global myarray() - make sure nothing carries over
    For x = 0 To 10 'or whatever the length of your list is
      myarray(x) = ""
    Next x
    
' Loop through the selected items in the list box and fill out myarray()
    If Me!lstRebate_Period.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstRebate_Period.ItemsSelected
            myarray(varItem) = Me!lstRebate_Period.ItemData(varItem)
        Next varItem
    End If
 
' Open the query
    DoCmd.OpenQuery "Query"

It appears to work. Open to suggestions.