passing a global variable to query

**Starasoris** · Oct 16 '06, 04:46 PM

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

WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=Air Quality));

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

ProjectKeyWords )="'" & get_global("Key WordsSelected") & "'"));

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!tx tKeywords & "'"
where txtKeywords is a text box wih the value. all depends on what is most appropriate.

Originally posted by wjfraser

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("Ke yWordsSelected" )

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 (((tblSubProjec tKeyWords.Proje ctKeyWords)='Ai r Quality'));

However, when I use

WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

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

**wjfraser** · Oct 16 '06, 05:21 PM

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

Originally posted by Starasoris

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

WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=Air Quality));

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

ProjectKeyWords )="'" & get_global("Key WordsSelected") & "'"));

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!tx tKeywords & "'"
where txtKeywords is a text box wih the value. all depends on what is most appropriate.

**MMcCarthy** · Oct 16 '06, 05:26 PM

Try adding this to the select part of your query:

SELECT field1, field2, etc, get_global("Key WordsSelected") As tmpValue
FROM Table
WHERE tblSubProjectKe yWords.ProjectK eyWords=tmpValu e;

Originally posted by wjfraser

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("Ke yWordsSelected" )

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 (((tblSubProjec tKeyWords.Proje ctKeyWords)='Ai r Quality'));

However, when I use

WHERE (((tblSubProjec tKeyWords.Proje ctKeyWords)=get _global("KeyWor dsSelected")));

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

**wjfraser** · Oct 16 '06, 07:52 PM

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_KeyWordsSel ected

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

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

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

Originally posted by Starasoris

...

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

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

**wjfraser** · Oct 16 '06, 07:54 PM

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

Originally posted by mmccarthy

Try adding this to the select part of your query:

SELECT field1, field2, etc, get_global("Key WordsSelected") As tmpValue
FROM Table
WHERE tblSubProjectKe yWords.ProjectK eyWords=tmpValu e;

**MMcCarthy** · Oct 16 '06, 08:09 PM

Whitney

If this doesn't work you have another problem.

WHERE (([tblSubProjectKe yWords].[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 (([tblSubProjectKe yWords].[ProjectKeyWords] "*" & [Forms]![frmKWtoStaff]![txtHoldSelected] & "*"));

**wjfraser** · Oct 16 '06, 08:34 PM

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:

Code:

Public Function get_global(G_name As String)

' property of blueclaw-db.com
'
     Select Case G_name
            Case "KeyWordsSelected"
                    get_global = GBL_KeyWordsSelected
            Case "KeyWordsSelectedRaw"
                    get_global = GBL_KeyWordsSelectedRaw
            Case "StaffSelected"
                    get_global = GBL_StaffSelected
    End Select
End Function

And this is the global declaration section:

Code:

Global GBL_KeyWordsSelectedRaw As String
Global GBL_KeyWordsSelected As String
Global GBL_StaffSelected As String

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

Originally posted by mmccarthy

Whitney

If this doesn't work you have another problem.

WHERE (([tblSubProjectKe yWords].[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 (([tblSubProjectKe yWords].[ProjectKeyWords] "*" & [Forms]![frmKWtoStaff]![txtHoldSelected] & "*"));

**MMcCarthy** · Oct 16 '06, 08:39 PM

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:

Code:

 
Public Function get_global(G_name As String) As String

' property of blueclaw-db.com
'
	 Select Case G_name
			Case "KeyWordsSelected"
					get_global = GBL_KeyWordsSelected
			Case "KeyWordsSelectedRaw"
					get_global = GBL_KeyWordsSelectedRaw
			Case "StaffSelected"
					get_global = GBL_StaffSelected
	End Select

End Function

**wjfraser** · Oct 16 '06, 08:54 PM

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.

Originally posted by mmccarthy

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:

Code:

 
Public Function get_global(G_name As String) As String

' property of blueclaw-db.com
'
	 Select Case G_name
			Case "KeyWordsSelected"
					get_global = GBL_KeyWordsSelected
			Case "KeyWordsSelectedRaw"
					get_global = GBL_KeyWordsSelectedRaw
			Case "StaffSelected"
					get_global = GBL_StaffSelected
	End Select

End Function

**MMcCarthy** · Oct 16 '06, 09:08 PM

Can you post the full query here.

**wjfraser** · Oct 16 '06, 09:20 PM

Originally posted by mmccarthy

Can you post the full query here.

Sure thing. This is the original that I started with:

Code:

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

This is the one that doesn't use get_global:

Code:

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

This is a query that works properly:

Code:

SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
FROM tblSubProjectKeyWords
WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]='Air Quality'));

However, even when it appears that get_global("Key WordsSelected") 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

**MMcCarthy** · Oct 16 '06, 09:40 PM

Are you creating this query in query design or using code?

**wjfraser** · Oct 16 '06, 09:44 PM

Originally posted by mmccarthy

Are you creating this query in query design or using code?

Using code.

**MMcCarthy** · Oct 16 '06, 09:53 PM

Sorry I was working on the wrong assumption:

Code:

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

And for the one that doesn't use get_global:

Code:

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

passing a global variable to query

passing a global variable to query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment