By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,152 Members | 2,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,152 IT Pros & Developers. It's quick & easy.

passing a global variable to query

P: 21
Hello,

I am trying to pass the value in a global variable to a query. I know this can't be done explicitly, but I've used a small function called get_global that returns the value of the variable. When I do this:

msgBox (get_global("KeyWordsSelected")

I get a message box with exactly the value I want (I am just doing this for debugging purposes; it is not a crucial element of the application). In this example, say it returns

'Air Quality'

When I take this value (that's in the message box) and copy it into my query, it works perfectly. For example:

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)='Air Quality'));

However, when I use

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=get_glob al("KeyWordsSelected")));

I get nothing returned.

I am trying to cobble this together knowing little about SQL or Access, so I would appreciate any advice. Is there some syntax rule I am not following?

Thanks!
Whitney
Oct 16 '06 #1
Share this Question
Share on Google+
27 Replies


P: 11
It is most likely the quote issue. What it would be getting evaluated to is

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=Air Quality));

you may need to append the 's. Try the following. I am appending a single quote inside double quotes.

ProjectKeyWords)="'" & get_global("KeyWordsSelected") & "'"));

Functions are evaluated before the SQL logic is evaluated so if you think of it step by step, you will understand what the SQL parser wantes to see.

Along with globals, you can of course reference the value directly from a form if it is possible.

ProjectKeyWords="'" & Forms!MyForm!txtKeywords & "'"
where txtKeywords is a text box wih the value. all depends on what is most appropriate.

Hello,

I am trying to pass the value in a global variable to a query. I know this can't be done explicitly, but I've used a small function called get_global that returns the value of the variable. When I do this:

msgBox (get_global("KeyWordsSelected")

I get a message box with exactly the value I want (I am just doing this for debugging purposes; it is not a crucial element of the application). In this example, say it returns

'Air Quality'

When I take this value (that's in the message box) and copy it into my query, it works perfectly. For example:

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)='Air Quality'));

However, when I use

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=get_glob al("KeyWordsSelected")));

I get nothing returned.

I am trying to cobble this together knowing little about SQL or Access, so I would appreciate any advice. Is there some syntax rule I am not following?

Thanks!
Whitney
Oct 16 '06 #2

P: 21
Thanks for the idea. Unfortunately it did not work. I do append the single quotes in the variable itself, so literally the variable contains the value

'Air Quality'

with the single quotes already on. Could they be stripped somehow in the parsing process?

I will look into your second suggestion too, thanks.

Whitney

It is most likely the quote issue. What it would be getting evaluated to is

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=Air Quality));

you may need to append the 's. Try the following. I am appending a single quote inside double quotes.

ProjectKeyWords)="'" & get_global("KeyWordsSelected") & "'"));

Functions are evaluated before the SQL logic is evaluated so if you think of it step by step, you will understand what the SQL parser wantes to see.

Along with globals, you can of course reference the value directly from a form if it is possible.

ProjectKeyWords="'" & Forms!MyForm!txtKeywords & "'"
where txtKeywords is a text box wih the value. all depends on what is most appropriate.
Oct 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try adding this to the select part of your query:

SELECT field1, field2, etc, get_global("KeyWordsSelected") As tmpValue
FROM Table
WHERE tblSubProjectKeyWords.ProjectKeyWords=tmpValue;


Hello,

I am trying to pass the value in a global variable to a query. I know this can't be done explicitly, but I've used a small function called get_global that returns the value of the variable. When I do this:

msgBox (get_global("KeyWordsSelected")

I get a message box with exactly the value I want (I am just doing this for debugging purposes; it is not a crucial element of the application). In this example, say it returns

'Air Quality'

When I take this value (that's in the message box) and copy it into my query, it works perfectly. For example:

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)='Air Quality'));

However, when I use

WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=get_glob al("KeyWordsSelected")));

I get nothing returned.

I am trying to cobble this together knowing little about SQL or Access, so I would appreciate any advice. Is there some syntax rule I am not following?

Thanks!
Whitney
Oct 16 '06 #4

P: 21
I tried this second suggestion and it seems to work the same way as the msgBox. It prints the correct value in the text box on the form, but when I paste the same reference into the SQL query it doesn't work.

This puts the global value into the text box (works!):

[Forms]![frmKWtoStaff]![txtHoldSelected] = GBL_KeyWordsSelected

and this is the WHERE statement (doesn't work!):

WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=[Forms]![frmKWtoStaff]![txtHoldSelected]));

I also tried it with appending additional single quotes ("'" & value & "'") but that had no effect.


...

Along with globals, you can of course reference the value directly from a form if it is possible.

ProjectKeyWords="'" & Forms!MyForm!txtKeywords & "'"
where txtKeywords is a text box wih the value. all depends on what is most appropriate.
Oct 16 '06 #5

P: 21
Do I have to declare tmpValue anywhere or take any other preparatory steps? When I try this, it just pops up a box asking for a value for tmpValue like it is a simple parameter query.

Thanks,
Whitney

Try adding this to the select part of your query:

SELECT field1, field2, etc, get_global("KeyWordsSelected") As tmpValue
FROM Table
WHERE tblSubProjectKeyWords.ProjectKeyWords=tmpValue;
Oct 16 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Whitney

If this doesn't work you have another problem.

WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=[Forms]![frmKWtoStaff]![txtHoldSelected]));

What is the data type of the value being returned by get_global?
What is the data type of the field ProjectKeyWords?
Is the form open when this query is being run?

Try this:

WHERE (([tblSubProjectKeyWords].[ProjectKeyWords] "*" & [Forms]![frmKWtoStaff]![txtHoldSelected] & "*"));
Oct 16 '06 #7

P: 21
Thanks for your continued help. The query you gave below with "*"s doesn't work either.

The data type of the field ProjectKeyWords is text.
get_global should be returning a string.

This is the get_global function:
Expand|Select|Wrap|Line Numbers
  1. Public Function get_global(G_name As String)
  2.  
  3. ' property of blueclaw-db.com
  4. '
  5.      Select Case G_name
  6.             Case "KeyWordsSelected"
  7.                     get_global = GBL_KeyWordsSelected
  8.             Case "KeyWordsSelectedRaw"
  9.                     get_global = GBL_KeyWordsSelectedRaw
  10.             Case "StaffSelected"
  11.                     get_global = GBL_StaffSelected
  12.     End Select
  13. End Function
  14.  
And this is the global declaration section:

Expand|Select|Wrap|Line Numbers
  1. Global GBL_KeyWordsSelectedRaw As String
  2. Global GBL_KeyWordsSelected As String
  3. Global GBL_StaffSelected As String
  4.  
What I really don't get is why it seems to work when the output is to a msgBox or a text box, but the same statement doesn't translate for SQL.

Thanks,
Whitney

Whitney

If this doesn't work you have another problem.

WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=[Forms]![frmKWtoStaff]![txtHoldSelected]));

What is the data type of the value being returned by get_global?
What is the data type of the field ProjectKeyWords?
Is the form open when this query is being run?

Try this:

WHERE (([tblSubProjectKeyWords].[ProjectKeyWords] "*" & [Forms]![frmKWtoStaff]![txtHoldSelected] & "*"));
Oct 16 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I can't get why it's working at all you haven't declared the data type being returned from the function. It is probably defaulting to a variant. Try this instead:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function get_global(G_name As String) As String
  3.  
  4. ' property of blueclaw-db.com
  5. '
  6.      Select Case G_name
  7.             Case "KeyWordsSelected"
  8.                     get_global = GBL_KeyWordsSelected
  9.             Case "KeyWordsSelectedRaw"
  10.                     get_global = GBL_KeyWordsSelectedRaw
  11.             Case "StaffSelected"
  12.                     get_global = GBL_StaffSelected
  13.     End Select
  14.  
  15. End Function
  16.  
  17.  
Oct 16 '06 #9

P: 21
Thnks for sticking with me on this. Just to make sure I got what you said - the only thing you added was "As String" to the end of the first line, is that correct?

It still doesn't work though. Plus, per a different suggestion above, I wrote a different version where I didn't use the get_global function at all (instead storing the value in a text box), and it still didn't work, but the value came up correctly in the text box and in a msgBox.

I can't get why it's working at all you haven't declared the data type being returned from the function. It is probably defaulting to a variant. Try this instead:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function get_global(G_name As String) As String
  3.  
  4. ' property of blueclaw-db.com
  5. '
  6.      Select Case G_name
  7.             Case "KeyWordsSelected"
  8.                     get_global = GBL_KeyWordsSelected
  9.             Case "KeyWordsSelectedRaw"
  10.                     get_global = GBL_KeyWordsSelectedRaw
  11.             Case "StaffSelected"
  12.                     get_global = GBL_StaffSelected
  13.     End Select
  14.  
  15. End Function
  16.  
  17.  
Oct 16 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post the full query here.
Oct 16 '06 #11

P: 21
Can you post the full query here.
Sure thing. This is the original that I started with:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
  2. FROM tblSubProjectKeyWords
  3. WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=get_global("KeyWordsSelected")));
  4.  
This is the one that doesn't use get_global:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
  2. FROM tblSubProjectKeyWords
  3. WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=[Forms]![frmKWtoStaff]![txtHoldSelected]));
  4.  
This is a query that works properly:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
  2. FROM tblSubProjectKeyWords
  3. WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='Air Quality'));
  4.  
However, even when it appears that get_global("KeyWordsSelected") and [Forms]![frmKWtoStaff]![txtHoldSelected] resolve to 'Air Quality' (based on msgBox output - msgBox triggered after query is called), neither term resolves correctly within the query.

Thanks,
Whitney
Oct 16 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Are you creating this query in query design or using code?
Oct 16 '06 #13

P: 21
Are you creating this query in query design or using code?
Using code.
Oct 16 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry I was working on the wrong assumption:

Expand|Select|Wrap|Line Numbers
  1.  
  2. "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
  3. "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
  4. "FROM tblSubProjectKeyWords " & _
  5. "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
  6. & get_global("KeyWordsSelected") & "'));"
  7.  
  8.  
And for the one that doesn't use get_global:

Expand|Select|Wrap|Line Numbers
  1.  
  2. "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
  3. "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
  4. "FROM tblSubProjectKeyWords " & _
  5. "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
  6. & [Forms]![frmKWtoStaff]![txtHoldSelected] & "'));
  7.  
  8.  
Oct 16 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Should be quotation mark at the end of second example also.



Sorry I was working on the wrong assumption:

Expand|Select|Wrap|Line Numbers
  1.  
  2. "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
  3. "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
  4. "FROM tblSubProjectKeyWords " & _
  5. "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
  6. & get_global("KeyWordsSelected") & "'));"
  7.  
  8.  
And for the one that doesn't use get_global:

Expand|Select|Wrap|Line Numbers
  1.  
  2. "SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
  3. "[tblSubProjectKeyWords].[ProjectKeyWords] " & _
  4. "FROM tblSubProjectKeyWords " & _
  5. "WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
  6. & [Forms]![frmKWtoStaff]![txtHoldSelected] & "'));"
  7.  
  8.  
Oct 16 '06 #16

P: 21
Sorry I was working on the wrong assumption:
My apologies - I think I misunderstood your question. When you said query design, I thought you meant the Query Design window. I am using the SQL View of my query to work with it, not coding it elsewhere.
Oct 16 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Where did you put the get_global function. Is it in a module or in the code behind the form?



My apologies - I think I misunderstood your question. When you said query design, I thought you meant the Query Design window. I am using the SQL View of my query to work with it, not coding it elsewhere.
Oct 16 '06 #18

P: 21
Where did you put the get_global function. Is it in a module or in the code behind the form?
It is in a module.
Oct 16 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this:

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=get_global('KeyWordsSelected')));
Oct 16 '06 #20

P: 21
Same result - not working.

Try this:

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=get_global('KeyWordsSelected')));
Oct 16 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
Re-examine how the Gobal value is being assigned

In the function after

Case "KeyWordsSelected"
get_global = GBL_KeyWordsSelected

put

Msgbox GBL_KeyWordsSelect & " - " & get_global

Just to check what values are showing for both.
Oct 16 '06 #22

P: 21
Re-examine how the Gobal value is being assigned

In the function after

Case "KeyWordsSelected"
get_global = GBL_KeyWordsSelected

put

Msgbox GBL_KeyWordsSelect & " - " & get_global

Just to check what values are showing for both.
It shows identical values:

'Air Quality' - 'Air Quality'
Oct 16 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=get_glob al("KeyWordsSelected")));

I've tested the above and the syntax works fine. I can't figure out why this is not working and the next one is:

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (((tblSubProjectKeyWords.ProjectKeyWords)="Air Quality"));
Oct 16 '06 #24

P: 21
How frustrating. I pasted both of these statements into my query's SQL View, with the same results as ever (the get_global one didn't return anything but the hard-coded version worked fine).

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (((tblSubProjectKeyWords.ProjectKeyWords)=get_glob al("KeyWordsSelected")));

I've tested the above and the syntax works fine. I can't figure out why this is not working and the next one is:

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (((tblSubProjectKeyWords.ProjectKeyWords)="Air Quality"));
Oct 17 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
I can't understand it. There is nothing wrong with the syntax.

For some reason the get_global is not returning the string but based on what you've told me it should be.

How frustrating. I pasted both of these statements into my query's SQL View, with the same results as ever (the get_global one didn't return anything but the hard-coded version worked fine).
Oct 17 '06 #26

P: 21
Well, thank you for trying to work on it with me. Is there any other way to conceptualize a solution to this? The basic issue is that I want to pass selected items from a list box (key words) as criteria to end up with a list of projects using those key words. Each project has a number, and each project number can be associated with one or more key words. So, I have been using a form to let the user select the key words and then constructing an "OR" criteria statement with the selected words. In my examples I've just been using one word, because I haven't even gotten it to function at that level yet.

Thanks,
Whitney

I can't understand it. There is nothing wrong with the syntax.

For some reason the get_global is not returning the string but based on what you've told me it should be.
Oct 17 '06 #27

P: 21
For the future reference of anyone who encounters this same problem, I have gotten the query to work properly now, by building it completely in the code behind the button calling the query.

Expand|Select|Wrap|Line Numbers
  1. Set MyDB = CurrentDb()
  2.  
  3. strSQL = "SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords] " & _
  4. "FROM tblSubProjectKeyWords " & _
  5. "WHERE (([ProjectKeyWords]="
  6.  
  7. strSQL = strSQL & GBL_KeyWordsSelected & "))"
  8.  
  9. MyDB.QueryDefs.Delete "subqrySingleKW"
  10. Set qdef = MyDB.CreateQueryDef("subqrySingleKW", strSQL)
  11.  
  12. DoCmd.OpenQuery "subqrySingleKW", acViewNormal
  13.  
Oct 17 '06 #28

Post your reply

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