passing a global variable to query

[MMcCarthy]

Should be quotation mark at the end of second example also.

Sorry I was working on the wrong assumption:

 
"SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
"[tblSubProjectKeyWords].[ProjectKeyWords] " & _
"FROM tblSubProjectKeyWords " & _
"WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
& get_global("KeyWordsSelected") & "'));"

And for the one that doesn't use get_global:

 
"SELECT [tblSubProjectKeyWords].[ProjectNumber], " & _
"[tblSubProjectKeyWords].[ProjectKeyWords] " & _
"FROM tblSubProjectKeyWords " & _
"WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='" _
& [Forms]![frmKWtoStaff]![txtHoldSelected] & "'));"

[wjfraser]

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.

[MMcCarthy]

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.

[wjfraser]

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.

[MMcCarthy]

Try this:

SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (([tblSubProjectKe yWords].[ProjectKeyWords]=get_global('Ke yWordsSelected' )));

[wjfraser]

Same result - not working.

Try this:

SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (([tblSubProjectKe yWords].[ProjectKeyWords]=get_global('Ke yWordsSelected' )));

[MMcCarthy]

Re-examine how the Gobal value is being assigned

In the function after

Case "KeyWordsSelect ed"
get_global = GBL_KeyWordsSel ected

put

Msgbox GBL_KeyWordsSel ect & " - " & get_global

Just to check what values are showing for both.

[wjfraser]

Re-examine how the Gobal value is being assigned

In the function after

Case "KeyWordsSelect ed"
get_global = GBL_KeyWordsSel ected

put

Msgbox GBL_KeyWordsSel ect & " - " & get_global

Just to check what values are showing for both.

It shows identical values:

'Air Quality' - 'Air Quality'

[MMcCarthy]

SELECT [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

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 [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)="Ai r Quality"));

[wjfraser]

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 [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

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 [tblSubProjectKe yWords].[ProjectNumber], [tblSubProjectKe yWords].[ProjectKeyWords]
FROM tblSubProjectKe yWords
WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)="Ai r Quality"));

[MMcCarthy]

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).

[wjfraser]

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.

[wjfraser]

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.

Set MyDB = CurrentDb()

strSQL = "SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords] " & _
"FROM tblSubProjectKeyWords " & _
"WHERE (([ProjectKeyWords]="

strSQL = strSQL & GBL_KeyWordsSelected & "))"

MyDB.QueryDefs.Delete "subqrySingleKW"
Set qdef = MyDB.CreateQueryDef("subqrySingleKW", strSQL)
    
DoCmd.OpenQuery "subqrySingleKW", acViewNormal