DLookup and SQL statement for Control Source

[tgall]

Short Trip: Neither of the following work as a Control Source for an unbound text box. What am I doing wrong?

The following DLookup runs but gives a #Name? error:
=Nz(DLookUp("ACMinQuant","dttblAcceptanceCriteria" ,(("RepositoryID = " & "[Forms]![frm02EnterTestData]![RepositoryID]") AND ("MeasAttribute = "FlowRateLPMaxcc"))))

The following SQL, which is a copy and paste from a query, does not run in the Control Source and gives a "The syntax of the subquery in tis expression in incorrect." error:
SELECT dttblAcceptanceCriteria.RepositoryID, dttblAcceptanceCriteria.MeasAttribute, dttblAcceptanceCriteria.ACMinQuant
FROM dttblAcceptanceCriteria
WHERE (((dttblAcceptanceCriteria.RepositoryID)=[Forms]![frm02EnterTestData]![RepositoryID]) AND ((dttblAcceptanceCriteria.MeasAttribute)="FlowRate LPMaxcc"));

Scenic View: On a form, I'm using an unbound text box to hold a number. The number is extracted from dttblAcceptanceCriteria:

MEASATTRIBUTE ... ACMINQUANT ... ACMAXQUANT ... REPOSITORYID
FlowRateLPMaxcc .. 7.2 .................. 30 ................... 184
FlowRateUPMaxcc .. 6.2 .................. 25 ................... 184



I first tried the following DLookup. It runs, but the result is #Name? and not 7.2. The function doesn't like the AND ~~~~
=Nz(DLookUp("ACMinQuant","dttblAcceptanceCriteria" ,(("RepositoryID = " & "[Forms]![frm02EnterTestData]![RepositoryID]") AND ("MeasAttribute = "FlowRateLPMaxcc"))))

So, I tried SQL code. I built the code in a query, which runs. But, when I copy the SELECT statement into the Control Source, the following pithy error message pops up:
The syntax of the subquery in this expression incorrect.
SELECT dttblAcceptanceCriteria.RepositoryID, dttblAcceptanceCriteria.MeasAttribute, dttblAcceptanceCriteria.ACMinQuant
FROM dttblAcceptanceCriteria
WHERE (((dttblAcceptanceCriteria.RepositoryID)=[Forms]![frm02EnterTestData]![RepositoryID]) AND ((dttblAcceptanceCriteria.MeasAttribute)="FlowRate LPMaxcc"));

What am I doing wrong?

[pbaldy]

You can't have SQL as the control source. See if this helps with the DLookup():

http://access.mvps.org/access/general/gen0018.htm

[John_G]

You have the forms!... reference inside quotation marks, which causes Access to interpret it as text. Try this:

=Nz(DLookUp("ACMinQuant","dttblAcceptanceCriteria" ,"RepositoryID = " & [Forms]![frm02EnterTestData]![RepositoryID] & " AND MeasAttribute = 'FlowRateLPMaxcc'"))

Note the single quotes around FlowRateLPMaxcc.

[pbaldy]

And there's your fish.

[tgall]

Paul, thank you for responding. The models did not help. I have lots of models and they confuse me:
* always use NZ; no, NZ is unnecessary
* use braces; no, use double quotes
* use double quotes; no, use single quotes enclosed in double quotes
* use bangs; no, use periods
* use quotes for table but braces for field
* ensure there's space before and after the = sign; no, Access will insert the correct spaces
* if it's Wednesday but the moon is full, use squiggly braces otherwise use ancient Sumerian

I believe it's the AND ~~~ portion that is the discombobulation, but I don't know what Access wants.
When I set this up in a query, I get the correct answer (see SQL above).

When I remove the AND~~~~ portion, a number appears (7.2). But when I set up an unbound text
box for the second MeasAttribute for RepositoryID = 184 the number is 7.2 (not 6.2). I'm assuming
DLookup is extracting the first instance it finds.

So, I gotta have the AND~~~~~ portion. What does Access want?

[tgall]

John, thank you!!! The code works. I'm dancin' an Irish jig.

You've done so much, but I have to call upon you again, please.
I need to learn when to use bangs and periods and braces and double-quotes and single-quotes.
Do you know of a website that explains that (video tutorials are not good websites
for me -- I need documentation and examples).