DLookUp #Error

[Megaton]

Hello
Could anyone help me solve this problem please? I have been looking at this
expression for an hour and just couldn't figure out where the problem is.

=DLookUp("TotalUnit","OpeningBal","[ProductName] = " & [ProductName])

This expression is in a report where I would like to pull some total number
for each product out of a query "openingbal"

The openingbal query is as follows, the txFrom datetime value is provided.
SELECT ProductName, Sum(nz([Purchased])-nz([Given])) AS TotalUnit
FROM Transactions
WHERE TransactionDate<Forms!ReportViews!txFrom
GROUP BY ProductName
ORDER BY ProductName;

Thank you in advance.

 

[fredg]

Hello
Could anyone help me solve this problem please? I have been looking at this
expression for an hour and just couldn't figure out where the problem is.

=DLookUp("TotalUnit","OpeningBal","[ProductName] = " & [ProductName])

This expression is in a report where I would like to pull some total number
for each product out of a query "openingbal"

The openingbal query is as follows, the txFrom datetime value is provided.
SELECT ProductName, Sum(nz([Purchased])-nz([Given])) AS TotalUnit
FROM Transactions
WHERE TransactionDate<Forms!ReportViews!txFrom
GROUP BY ProductName
ORDER BY ProductName;

Thank you in advance.

1) The above expression is the control source of a control in the
report?
Make sure the name of this control is NOT the same as the name of any
field used in the expression.

2) What datatype is [ProductName]?
If it is a number datatype your expression should return the first
instance (just one record) of [TotalUnit] in the query whose
[ProductName] field matches the criteria, i.e. "[ProductName] = 123"

If it is a Text datatype, then your expression is incorrectly written.
Try:
=DLookUp("TotalUnit","OpeningBal","[ProductName] = '" & [ProductName]
& "'")

In this case "[ProductName] = 'Oranges'"

 

[Megaton]

Thank you Fred

1) The expression is the control source and name is unique
2) It is a text datatype. I tried your way but it still gives me #error.

 

[Megaton]

Is there any way to debug DLookUp expression?
Thanks

Thank you Fred

1) The expression is the control source and name is unique
2) It is a text datatype. I tried your way but it still gives me #error.

1) The above expression is the control source of a control in the
report?
Make sure the name of this control is NOT the same as the name of any
field used in the expression.

2) What datatype is [ProductName]?
If it is a number datatype your expression should return the first
instance (just one record) of [TotalUnit] in the query whose
[ProductName] field matches the criteria, i.e. "[ProductName] = 123"

If it is a Text datatype, then your expression is incorrectly written.
Try:
=DLookUp("TotalUnit","OpeningBal","[ProductName] = '" & [ProductName]
& "'")

In this case "[ProductName] = 'Oranges'"


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

 

[Steve]

Megaton,

I think the issue is that your parameter name [productname] is the
same as the field name [productname], try changing the parameter to a
different name e.g.[ProdName]

 

[Duane Hookom]

It should be much more efficient to add the OpeningBal query to your report's
record source and join the ProductName fields. Add [TotalUnit] to the field
list so you can use it in the report.

--
Duane Hookom
Microsoft Access MVP

Is there any way to debug DLookUp expression?
Thanks

Thank you Fred

1) The expression is the control source and name is unique
2) It is a text datatype. I tried your way but it still gives me #error.

1) The above expression is the control source of a control in the
report?
Make sure the name of this control is NOT the same as the name of any
field used in the expression.

2) What datatype is [ProductName]?
If it is a number datatype your expression should return the first
instance (just one record) of [TotalUnit] in the query whose
[ProductName] field matches the criteria, i.e. "[ProductName] = 123"

If it is a Text datatype, then your expression is incorrectly written.
Try:
=DLookUp("TotalUnit","OpeningBal","[ProductName] = '" & [ProductName]
& "'")

In this case "[ProductName] = 'Oranges'"


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

 

[Megaton]

Thanks Duane.
I know it works for sure, but I have already used another bigger query for
the report's record source. That query also conflicts with this "openingbal".
I don't know what to do....

It should be much more efficient to add the OpeningBal query to your report's
record source and join the ProductName fields. Add [TotalUnit] to the field
list so you can use it in the report.

--
Duane Hookom
Microsoft Access MVP

Is there any way to debug DLookUp expression?
Thanks

Thank you Fred

1) The expression is the control source and name is unique
2) It is a text datatype. I tried your way but it still gives me #error.



:

1) The above expression is the control source of a control in the
report?
Make sure the name of this control is NOT the same as the name of any
field used in the expression.

2) What datatype is [ProductName]?
If it is a number datatype your expression should return the first
instance (just one record) of [TotalUnit] in the query whose
[ProductName] field matches the criteria, i.e. "[ProductName] = 123"

If it is a Text datatype, then your expression is incorrectly written.
Try:
=DLookUp("TotalUnit","OpeningBal","[ProductName] = '" & [ProductName]
& "'")

In this case "[ProductName] = 'Oranges'"


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

 

[Duane Hookom]

Fredg's suggestion should work. If it doesn't, please come back with these
properties of the text box:

Name:
Control Source:

--
Duane Hookom
Microsoft Access MVP

Thanks Duane.
I know it works for sure, but I have already used another bigger query for
the report's record source. That query also conflicts with this "openingbal".
I don't know what to do....

It should be much more efficient to add the OpeningBal query to your report's
record source and join the ProductName fields. Add [TotalUnit] to the field
list so you can use it in the report.

--
Duane Hookom
Microsoft Access MVP

Is there any way to debug DLookUp expression?
Thanks

:

Thank you Fred

1) The expression is the control source and name is unique
2) It is a text datatype. I tried your way but it still gives me #error.



:

1) The above expression is the control source of a control in the
report?
Make sure the name of this control is NOT the same as the name of any
field used in the expression.

2) What datatype is [ProductName]?
If it is a number datatype your expression should return the first
instance (just one record) of [TotalUnit] in the query whose
[ProductName] field matches the criteria, i.e. "[ProductName] = 123"

If it is a Text datatype, then your expression is incorrectly written.
Try:
=DLookUp("TotalUnit","OpeningBal","[ProductName] = '" & [ProductName]
& "'")

In this case "[ProductName] = 'Oranges'"


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.