ms access - when I select a query in vba, column is empty

1
votes

When i write a select statement in vba to grab a column from a query its empty.

i have a query thats joined by multiple tables.

For example if I call select query.specialcolumn from query where query.id=5 I get a blank back. However, If I view it in the query table I see data for ID=5 with data.

Straight SQL in design mode also produces blanks. Only when I view the query as a whole, I can see data.

Any ideas?

1
sqlms-accessvba
Are you able to post the SQL for the "query" as well please, on the face of it this sounds quite strange – Matt Donnan

1 Answers

3
votes

Sounds like you used "query" as the name for your saved query. And query is a reserved word, see Problem names and reserved words in Access. It's hard predict when reserved words as object names will create problems. And I'm not confident that name is the problem here. But I would rule it out first before investigating anything else.

Enclose query in square brackets everywhere it's referenced in the SQL.

select [query].specialcolumn from [query] where [query].id=5

The square brackets will inform the db engine that query is a database object rather than the reserved word.