Query with a variable won't work

[Kendall]

I am trying to write a query that will display only records that have a certain game_no. The game_no will come from a global variable called ref. When I try the following in Data Manager I get the error message Error opening table VB: Too few parameters. Expected 1.

SELECT Plays.*
FROM Plays
WHERE (((Plays.Game_no)=[ref]));

If I replace [ref] with ‘ref’, I get type mismatch.

If I put the same code in the form Load section I get the following error message:

SELECT Plays.* (Error Message EXPECTED: Case)
FROM Plays
WHERE (((Plays.Game_no)=[ref]))


Any suggestions?

[KentJ]

Hello Kendall,
The problem is that the whole query needs to be a string, with the part to right of the = in quotes.

It amounts to just a syntax challenge ;-) Typically the query would look something like:
strQuery = "SELECT Plays.* _
FROM Plays _
WHERE (((Plays.Game_no)="""ref"""));"

I've also seen the following:
"...WHERE (((Plays.Game_no)=" & chr(AsciiQuote) & ref & chr(AsciiQuote)

BTW if ref is not a string, then use format(ref)

Good luck!