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

Question

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?

Are you able to post the SQL for the "query" as well please, on the face of it this sounds quite strange — Matt Donnan
– Matt Donnan, Commented Feb 17, 2012 at 17:00

HansUp · Accepted Answer · 2012-02-17 17:57:46Z

3

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.

answered Feb 17, 2012 at 17:57

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related