473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Passing global variable to query criteria

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



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.   Public Sub cmdOK_Click()
  5.  
  6. ' Declare variables
  7.     Dim db As DAO.Database
  8.     Dim varItem As Variant
  9.     Dim strSQL As String
  10.  
  11. ' Get the database and stored query
  12.     Set db = CurrentDb()
  13.  
  14.     DoCmd.RunSQL "Delete * From Table;"
  15.  
  16.     ' Loop through the selected items in the list box and build a text string
  17.     If Me!lstRebate_Period.ItemsSelected.Count > 0 Then
  18.         For Each varItem In Me!lstRebate_Period.ItemsSelected
  19.             strCriteria = strCriteria & "Like " & Chr(34) _
  20.                           & "*" & Me!lstRebate_Period.ItemData(varItem) & Chr(34) & "OR "
  21.         Next varItem
  22.         strCriteria = Left(strCriteria, Len(strCriteria) - 3)
  23.     Else
  24.         strCriteria = "Tbl_Payment_YTD.[Rebate Period] Like '*'"
  25.     End If
  26.  
  27. ' Open the query
  28.     DoCmd.OpenQuery "Query"
  29.  
  30. Set db = Nothing
  31.  
  32. End Sub
Module:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public strCriteria As String
  5.  
  6. Public Function ReturnStrCriteria() As String
  7.  
  8. ReturnStrCriteria = strCriteria
  9.  
  10. End Function
Mar 24 '08 #1
11 7316
Stewart Ross
2,545 Expert Mod 2GB
Hi. In the button click code as you have provided it there is no call to the function ReturnStrCriteria() 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=ReturnStrCriteria() but there isn't a statement like this in your code.

-Stewart
Mar 24 '08 #2
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.
Mar 24 '08 #3
I tried adding the single quote but no luck. It works fine if I try to pass something like "January_2007".

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

Expand|Select|Wrap|Line Numbers
  1. Function ReturnStrCriteria() As String
  2. strCriteria = "Like '" & "*"
  3. ReturnStrCriteria = strCriteria
  4.  
  5. End Function
Not sure what I'm missing.
Mar 24 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
Expand|Select|Wrap|Line Numbers
  1. Function ReturnStrCriteria() As String
  2. strCriteria = "Like '" & "*"
  3. ReturnStrCriteria = strCriteria
  4. 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
Mar 24 '08 #5
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 ReturnStrCriteria() in the criteria of the query. Any suggestions on how I could accomplish this using a different method would be appreciated.
Mar 24 '08 #6
Stewart Ross
2,545 Expert Mod 2GB
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 ReturnStrCriteria() 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:
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "Like *" & Nz(Right$(Me!lstRebatePeriod, 4)) & "*"
If your month+year combination is in any other column, use
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "Like *" & Nz(Right$(Me!lstRebatePeriod.Column(n), 4)) & "*"
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter as String
  2. strFilter = "[field name to filter] Like *" & Right$(Me!lstRebatePeriod , 4) & "*" ' assuming as before that the year is in the first returned column, column 0
  3.  
  4. Me.Filter =strFilter
  5. Me.FilterOn = True
Good luck with your query.

-Stewart
Mar 24 '08 #7
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.
Mar 25 '08 #8
Stewart Ross
2,545 Expert Mod 2GB
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
Mar 25 '08 #9
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 "*" & ReturnStrCriteria() (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.


Expand|Select|Wrap|Line Numbers
  1. 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] )
  2. 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]
  3. FROM (T2 INNER JOIN T3 ON T2.F5 = T3.F5) INNER JOIN tblF8_List ON T3.F8 = tblF8_List.F8_11
  4. WHERE (((T3.[Rebate Period]) Like "*" & Nz(forms![name of your form]![lstRebatePeriod]) & "*"));
Mar 25 '08 #10
Stewart Ross
2,545 Expert Mod 2GB
Expand|Select|Wrap|Line Numbers
  1. ...WHERE (((T3.[Rebate Period]) Like "*" & 
  2. 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
Mar 25 '08 #11
NeoPa
32,556 Expert Mod 16PB
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.
Mar 26 '08 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: domeceo | last post by:
can anyone tell me why I cannot pass values in a setTimeout function whenever I use this function it says "menu is undefined" after th alert. function imgOff(menu, num) { if (document.images) {...
2
by: Matthew Clement | last post by:
I'm currently building a form (called frmReports) to set the criteria for a query, but I'm having some trouble with syntax and hope that one of the guru's here can help me achieve what I'm do. ...
1
by: Jan | last post by:
Hi, I'm trying with a query. A criteria of the query is a global variable represented as a function. The variable is the result of a multiselect listbox. The function looks like this: Public...
4
by: Regnab | last post by:
How can I pass a variable (Dim Property As String) to the query criteria? I figure I could do it the long way and send it to a form and have the query access it from there but I figure there has...
2
by: Keith | last post by:
Good Afternoon, New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared...
7
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from...
19
MSeda
by: MSeda | last post by:
I would like to use a Global variable as a criteria in an access query. in my first attempt to do so I wrote a Function that looks like this Public Function GloVar(ByVal VarName) As String ...
11
by: kennthompson | last post by:
Trouble passing mysql table name in php. If I use an existing table name already defined everything works fine as the following script illustrates. <?php function fms_get_info() { $result =...
5
by: RacerX2000 | last post by:
I have an MS access Database (2000) and Have created a form that sets a variable to a value I would like (Based on other selections in the form) to pass to my query criteria and I get the following...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.