Query Works By Itself, But Not in a Control

[ey.markov]

Greetings,

I have a simple query (Access 2000 on W2kPro):
SELECT [tblBankInfo].[CashAcct]
FROM tblBankInfo INNER JOIN tblPartnerships ON
[tblBankInfo].[AcctNumber]=[tblPartnerships].[AcctNumber]
WHERE [tblPartnerships].[PshpID]=[Forms]![frmTransMain]![PshpID];

which runs fine by itself. However, when I put it into ControlSource of
a text box on a report (enclosed in brackets, without a semicolon at
the end, and with = preceding), the control displays #Error?

The name of the control is txtCashAcct; the frmTransMain is open and
has a valid PshpID selected. No functions in the control, only the SQL
quoted above. What doesn't it like?

Thanks,
Yisroel

[Allen Browne]

You cannot put an SQL statement into the Control Source of a text box.

Save the query.

Then use:
=DLookup("CashA cct", "Query1")
replacing Query1 with the name of your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ey*******@inam e.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

Greetings,

I have a simple query (Access 2000 on W2kPro):
SELECT [tblBankInfo].[CashAcct]
FROM tblBankInfo INNER JOIN tblPartnerships ON
[tblBankInfo].[AcctNumber]=[tblPartnerships].[AcctNumber]
WHERE [tblPartnerships].[PshpID]=[Forms]![frmTransMain]![PshpID];

which runs fine by itself. However, when I put it into ControlSource of
a text box on a report (enclosed in brackets, without a semicolon at
the end, and with = preceding), the control displays #Error?

The name of the control is txtCashAcct; the frmTransMain is open and
has a valid PshpID selected. No functions in the control, only the SQL
quoted above. What doesn't it like?

Thanks,
Yisroel

[ey.markov]

Allen Browne wrote:

You cannot put an SQL statement into the Control Source of a text box.

Save the query.

Then use:
=DLookup("CashA cct", "Query1")
replacing Query1 with the name of your query.

Aha! Then the statement in Help and a book on Access I have -- "any
valid Access expression can be a control data source" -- needs
qualification: "unless it's an SQL expression."

Thank you very much, Allen. This works beautifully.

Yisroel

[Larry Linson]

<ey*******@inam e.comwrote

>You cannot put an SQL statement
into the Control Source of a text box.

Save the query.

Then use:
=DLookup("CashA cct", "Query1")
replacing Query1 with the name of your query.

Aha! Then the statement in Help and a
book on Access I have -- "any valid
Access expression can be a control data
source" -- needs qualification: "unless
it's an SQL expression."

SQL is not an "Access expression."

Larry Linson
Microsoft Access MVP