Passing global variable to query criteria

[Purdue02]

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 ReturnStrCriter ia() 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

[Stewart Ross]

Hi. In the button click code as you have provided it there is no call to the function ReturnStrCriter ia() at all. It is not being used to set your criterion string at present.

I cannot see where it would go, as you have a FOR loop building your criterion string from the contents of a list box at present.

Anyway, a call which sets your local variable to the value of the global would be of the form StrCriteria=Ret urnStrCriteria( ) but there isn't a statement like this in your code.

-Stewart

[WiscCard]

Maybe try adding this to the following line:

strCriteria = strCriteria & "Like '" & Chr(34) _

All I did was add an ' after the Like, but whenever I use a like function to return results, that works for me.

[Purdue02]

I tried adding the single quote but no luck. It works fine if I try to pass something like "January_20 07".

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

Function ReturnStrCriteria() As String
strCriteria = "Like '" & "*"
ReturnStrCriteria = strCriteria

End Function

Not sure what I'm missing.

[Stewart Ross]

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

Hi. Not sure what you were trying to do above - if you just wanted to include the wildcard you would do that in the criteria string directly. (The single quote above is incorrect - it is not needed at all unless you are including a value from, say, a control as a string literal, and if you were there would be a closing single quote as well.)

Questions which you would have to answer to give us some clue what you are trying to achieve: why are you using a global variable? (there is no clue above.) Where is it set? (it is not set in the code you have provided.) Why is there no call to the function you have created to return the value of the global variable? (see post #2 above.)

Further: is the user entering a value into a textbox or listbox and you wish to find all records like that value? You don't need the global or the unused function if what you want is to get a value from the user, find all matching records, and show those matching. We can help you with this, but you need to tell us what you are trying to do, and which fields whatever it is applies to. Without that all we can do is to point out that your function is not being applied, your global is not being set (so far as we can tell), and the comparison string has no direct link to what you have written in post #4.

-Stewart

[Purdue02]

I have a list box on a form that is populated with rebate period year (i.e. 2007, 2008). When the user selects a year and clicks on the OK button, a query is ran which searches through the rebate_period field for any records with the selected year. The field also includes the month (i.e. January_2007), so the criteria for the query needs to be "Like *2007". Currently, I am calling ReturnStrCriter ia() in the criteria of the query. Any suggestions on how I could accomplish this using a different method would be appreciated.

[Stewart Ross]

I have a list box on a form that is populated with rebate period year (i.e. 2007, 2008). When the user selects a year and clicks on the OK button, a query is ran which searches through the rebate_period field for any records with the selected year. The field also includes the month (i.e. January_2007), so the criteria for the query needs to be "Like *2007". Currently, I am calling ReturnStrCriter ia() in the criteria of the query. Any suggestions on how I could accomplish this using a different method would be appreciated.

Thanks, this helps in making it clearer what you are trying to achieve.

If the year is always returned along with the month you will need to extract the year from the rest of the string using the Right$ function to do so. Assuming that you are returning the month+year combination in the first column of your list box the VB code for this is just:
[code=vb]strCriteria = "Like *" & Nz(Right$(Me!ls tRebatePeriod, 4)) & "*"[/code]
If your month+year combination is in any other column, use
[code=vb]strCriteria = "Like *" & Nz(Right$(Me!ls tRebatePeriod.C olumn(n), 4)) & "*"[/code]
where n is 1 for column 2, 2 for column 3 and so on (columns are numbered from 0).

The Nz function returns an empty string if your list box is null (no selection made). As you will have a valid comparison whether or not a selection has been made from the listbox you can then do away with your IF statement altogether and use just the one line to set up your global.

I do think you have adopted a relatively torturous approach to something relatively straightforward , however. You can refer directly to the form field in your query without using a global to pass the value at all. The criterion would be:

like "*" & Nz(Right$(forms![name of your form]![lstRebatePeriod], 4)) & "*"

If you are trying to filter the records shown on the current form another approach is to apply a filter to the form itself. A skeleton for this is shown below.
[code=vb]Dim strFilter as String
strFilter = "[field name to filter] Like *" & Right$(Me!lstRe batePeriod , 4) & "*" ' assuming as before that the year is in the first returned column, column 0

Me.Filter =strFilter
Me.FilterOn = True[/code]
Good luck with your query.

-Stewart

[Purdue02]

Stewart,

Thanks for your response! I tried implementing the solution where the criteria in the query references the form directly, but I cannot get it to filter the results. Below is what I am using as the criteria (Excluded the Right function as Year is only populated in the list box).

Like "*" & Nz(forms![name of your form]![lstRebatePeriod]) & "*"

I am using a value list to populate the list box if that makes any difference.

[Stewart Ross]

Stewart,

Thanks for your response! I tried implementing the solution where the criteria in the query references the form directly, but I cannot get it to filter the results. Below is what I am using as the criteria (Excluded the Right function as Year is only populated in the list box).

Like "*" & Nz(forms![name of your form]![lstRebatePeriod]) & "*"

I am using a value list to populate the list box if that makes any difference.

Hi - glad you are making progress! Could you post the SQL for your query as you currently have it - this should help tie down why the LIKE clause is apparently not functioning. Is it in the underlying data that you have the month and year together, and not the list box? I am still a bit fuzzy about which part does what.

It would also help to see some example data along with your SQL.

Cheers

Stewart

[Purdue02]

Stewart,

I had posted this to another forum also and I got a response that has seemed to solve my problem. It was suggested that Access was having trouble interpreting the Like statement in the string and the following solution was given:

Like "*" & ReturnStrCriter ia() (Inserted into the query criteria)

*Just noticed this does not work when making multiple selections in the list box


This returns the results that I was expecting.

Still not sure as to why the direct reference to the list box is not working. The field does contain the month/year and the list box only contains the year (value list). Below is the sql, but I am not able to post the underlying data.

INSERT INTO T1 ( F1, [F2], [F3], F5, F6, F7, [F4], State, F8, DESCRIPTION, [F9], [F10], [Rebate Per Unit], [Rebate Dollars], [Rebate Period], [Memo], [Date Appended], [Person Appending], Processor, [Notes:], [PACKAGE COUNT] )
SELECT T2.F1, T2.[F2], T3.[F3], T3.F5, T3.F6, T3.F7, T3.[F4], T3.State, T3.F8, T3.DESCRIPTION, T3.[F9], T3.[F10], T3.[Rebate Per Unit], T3.[Rebate Dollars], T3.[Rebate Period], T3.Memo, T3.[Date Appended], T3.[Person Appending], T3.Processor, T3.[Notes:], tblF8_List.[PACKAGE COUNT]
FROM (T2 INNER JOIN T3 ON T2.F5 = T3.F5) INNER JOIN tblF8_List ON T3.F8 = tblF8_List.F8_11
WHERE (((T3.[Rebate Period]) Like "*" & Nz(forms![name of your form]![lstRebatePeriod]) & "*"));

[Stewart Ross]

...WHERE (((T3.[Rebate Period]) Like "*" & 
Nz(>>>forms![name of your form]![lstRebatePeriod]<<<) & "*"));

Hi. I notice you did not place the name of your form in the form reference above (as highlighted in triple chevrons. >>> and <<<. As I didn't (and don't) know what it is you would have to change it yourself when applying it to your own situation...

Anyway, I'm glad you have found a solution that works for you.

-Stewart

[NeoPa]

Sounds like a case for debugging (Debugging in VBA).

I think I get your original idea - it's sound. We just need to get it to work as intended.

Let me know when you're ready to handle the debugging part.