This Query statement with public variable

  • Thread starter Thread starter John Thomas
  • Start date Start date
J

John Thomas

ChkTxt is the public variable, but does not work, what is the proper
codeing. This is a access query, not a procedure.

SELECT Words.Word, Words.Synonyms, Words.Definition, Words.Grammar,
Words.Other
FROM [WordAnalysis.mdb].Words
WHERE (((Words.Word)=" & ChkTxt & "));
 
Replace
WHERE (((Words.Word)=" & ChkTxt & "));
Click to expand...
by
WHERE (((Words.Word)=" & chr(34) & ChkTxt & chr(34) & "));
or by
WHERE (((Words.Word)='" & ChkTxt & "'));

Note:
in first replacement chr(34) is the double quote text indicator.
in the second replacement, the first 'quote pair' start with a single
followed by a double quote.
The next quote pair kicks of with a double quote, followed by a single.

Explanation:
Y're trying to pass a string/text variable as comparison in the where
statement.
You forgot to include to text indicators, be it: chr(34) as examplified in
first replacement
or a single quote like in second replacement.

Krgrds,
Perry

John Thomas said:
ChkTxt is the public variable, but does not work, what is the proper
codeing. This is a access query, not a procedure.

SELECT Words.Word, Words.Synonyms, Words.Definition, Words.Grammar,
Words.Other
FROM [WordAnalysis.mdb].Words
WHERE (((Words.Word)=" & ChkTxt & "));
Click to expand...
 
Queries don't know anything about public variables, I'm afraid.

The easiest thing to do is to create a function that returns the value of
the variable:

As well, assuming that Word is a text field, you'll need to have quotes
around the string for which you're searching.

Function ReturnChkTxt() As String

ReturnChkTxt = ChkTxt

End Function

then

SELECT Words.Word, Words.Synonyms, Words.Definition, Words.Grammar,
Words.Other
FROM [WordAnalysis.mdb].Words
WHERE (((Words.Word)='" & ReturnChkTxt() & "'));

In case it's not obvious, the last line is:

WHERE (((Words.Word)=' " & ReturnChkTxt() & " ' ));

Alternatively, you could use

WHERE (((Words.Word)=" & Chr$(34) & ReturnChkTxt() & Chr$(34) & "));
 
Hi, John.

ChkTxt is not a key word, reserved word, SQL function, referenced library
function or a user-defined function, so Jet has no way of "knowing" what
ChkTxt is when the query runs. Create a user-defined public function in a
standard module to retrieve the value of this variable when the query runs,
and replace the ChkTxt variable in your query with the name of that
function.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Thank you,

I used the function, but got data mismatch until I tried it the following
way and it worked.

WHERE (((Words.Word)=ReturnChkTxt()));

Douglas J. Steele said:
Queries don't know anything about public variables, I'm afraid.

The easiest thing to do is to create a function that returns the value of
the variable:

As well, assuming that Word is a text field, you'll need to have quotes
around the string for which you're searching.

Function ReturnChkTxt() As String

ReturnChkTxt = ChkTxt

End Function

then

SELECT Words.Word, Words.Synonyms, Words.Definition, Words.Grammar,
Words.Other
FROM [WordAnalysis.mdb].Words
WHERE (((Words.Word)='" & ReturnChkTxt() & "'));

In case it's not obvious, the last line is:

WHERE (((Words.Word)=' " & ReturnChkTxt() & " ' ));

Alternatively, you could use

WHERE (((Words.Word)=" & Chr$(34) & ReturnChkTxt() & Chr$(34) & "));


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



John Thomas said:
ChkTxt is the public variable, but does not work, what is the proper
codeing. This is a access query, not a procedure.

SELECT Words.Word, Words.Synonyms, Words.Definition, Words.Grammar,
Words.Other
FROM [WordAnalysis.mdb].Words
WHERE (((Words.Word)=" & ChkTxt & "));
Click to expand...
Click to expand...
 
Back
Top