Query Results in unbound form field

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I am trying to use dlookup to put the results of a query (AAA) into a form's
unbound field

AAA
SELECT Sum(Payment_tbl.pmtAmt) AS SumOfpmtAmt, Year([pmtDate]) AS pmtYear,
Month([pmtDate]) AS pmtMo
FROM Payment_tbl
GROUP BY Year([pmtDate]), Month([pmtDate])
HAVING (((Year([pmtDate]))=Year(Now())));

Form Field
=DLookUp("pmtAmt","AAA",[pmtMo]=4)

getting an #Name error
 
I am trying to use dlookup to put the results of a query (AAA) into a form's
unbound field

AAA
SELECT Sum(Payment_tbl.pmtAmt) AS SumOfpmtAmt, Year([pmtDate]) AS pmtYear,
Month([pmtDate]) AS pmtMo
FROM Payment_tbl
GROUP BY Year([pmtDate]), Month([pmtDate])
HAVING (((Year([pmtDate]))=Year(Now())));

Form Field
=DLookUp("pmtAmt","AAA",[pmtMo]=4)

getting an #Name error
Click to expand...

You don't have a field named "pmtAmt" in the query.
You do have a field named "SumOfpmtAmt".
Is that the value you wish to display in the control?
Does the query return just one record? If yes then:

=DLookUp("[SumOfpmtAmt]","AAA")

If there are more than one record being returned by the query then you
have to use criteria to get the correct value. The criteria argument
must be a string.
=DLookUp("[SumOfpmtAmt]","AAA","[pmtMo] = 4")
 
Hi Brian,

The problem as I see it is the query does not have a field "pmtAmt" (the
"FieldToLookIn" in the DLookUp()).


Try:

Form Field
=DLookUp("SumOfpmtAmt","AAA",[pmtMo]=4)



HTH
 
Oh dang, it's always the simple things

Thanks, you guys are awsome !!!


fredg said:
I am trying to use dlookup to put the results of a query (AAA) into a form's
unbound field

AAA
SELECT Sum(Payment_tbl.pmtAmt) AS SumOfpmtAmt, Year([pmtDate]) AS pmtYear,
Month([pmtDate]) AS pmtMo
FROM Payment_tbl
GROUP BY Year([pmtDate]), Month([pmtDate])
HAVING (((Year([pmtDate]))=Year(Now())));

Form Field
=DLookUp("pmtAmt","AAA",[pmtMo]=4)

getting an #Name error
Click to expand...

You don't have a field named "pmtAmt" in the query.
You do have a field named "SumOfpmtAmt".
Is that the value you wish to display in the control?
Does the query return just one record? If yes then:

=DLookUp("[SumOfpmtAmt]","AAA")

If there are more than one record being returned by the query then you
have to use criteria to get the correct value. The criteria argument
must be a string.
=DLookUp("[SumOfpmtAmt]","AAA","[pmtMo] = 4")
Click to expand...
 
Back
Top