DLookUp #Error

M

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.
 
F

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'"
 
M

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.
 
M

Megaton

Is there any way to debug DLookUp expression?
Thanks

Megaton said:
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.



fredg said:
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
.
 
S

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]
 
D

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


Megaton said:
Is there any way to debug DLookUp expression?
Thanks

Megaton said:
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.



fredg said:
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
.
 
M

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....

Duane Hookom said:
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


Megaton said:
Is there any way to debug DLookUp expression?
Thanks

Megaton said:
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
.
 
D

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


Megaton said:
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....

Duane Hookom said:
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


Megaton said:
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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top