CODEWITHSUNDEEP
sqlms-accessms-access-2007
Matt Facer
Matt Facer

Reputation: 3105

SQL query in Access 2007 works, but not when in expression builder in report (results in #name?)

I have a saved query in MS Access 2007 named "test".

SELECT Count(system_info.id) AS CountOfUnique
FROM system_info;

So when I run this, it retuns 172 as expected. That confirms the query runs OK. The table "system_info" is a linked table to an excel spreadsheet. When I run any queries against that table, they ALL work in query view (datasheet).

However, when I try to reference that query result inside of a report, it doesn't work! Here's what I do

  1. Create new blank report
  2. Create a textbox
  3. click the "..." button to launch expression builder
  4. Using the expression builder, select the query and insert the result =[test]![CountOfUnique]

When I then run the report, the textbox has #Name? inside it. I've googled to death this issue and some suggest it could be a bug with MS Access 2007 when the query returns zero records. However, I know it returns one! Others have suggested changing the name of the textbox as it may clash with something else. Even when that textbox is on it's own in a brand new report it still doesn't work.

I'm tearing my hair out!! I can use the same method on other tables and I see the numbers, but it seems to be just this system_info table that doesnt work in the report. Is there a setting somewhere I've missed??

Thanks for any info

Upvotes: 2

Views: 4394

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

In the text box type:

=DlookUp("CountOfUnique","Test")

Also check out DCount.

Upvotes: 3

Related Questions