#Error in a report field

Can someone spot my issue here?  I'm getting a #Error when I use this for the control on a report field:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "John Doe ABC" & " AND [TransDate] = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "'")

I've also tried:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "John Doe ABC" & " AND [TransDate] =#" & [Reports]![rptInventoryOwnership]![txtCropYear] & "#")
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Looks like you are missing double quotes around the first literal, try below. (assuming the result query returns exactly one row always...)

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "'John Doe ABC'" & " AND [TransDate] = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "'")

Open in new window


»bp
0
SteveL13Author Commented:
I forgot one critera...  Crop

Here is what I have now but getting #Error

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "'John Doe ABC'" & " AND [Crop] = [txtCrop2] AND [TransDate] = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "'")
0
Bill PrewIT / Software Engineering ConsultantCommented:
What is txtCrop2?  A VBA variable?  A table column?  A control on the form?


»bp
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

SteveL13Author Commented:
Is a control on the report.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Double check me, but I think that would be:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "'John Doe ABC'" & " AND [Crop] = '" & [Reports]![rptInventoryOwnership]![txtCrop2] & "' AND [TransDate] = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "'")

Open in new window


»bp
0
Gustav BrockCIOCommented:
You need a properly formatted string expression for your date value:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = 'John Doe ABC' AND [TransDate] = #" & Format([Reports]![rptInventoryOwnership]![txtCropYear], "yyyy\/mm\/dd") & "#")

Open in new window

or - if it is not a date but a year:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = 'John Doe ABC' AND Year([TransDate]) = " & [Reports]![rptInventoryOwnership]![txtCropYear] & "")

Open in new window

0
SteveL13Author Commented:
Bill:  Still getting #Error

Gustav, per above:  (Crop has to be added as a criteria)

I forgot one criteria...  Crop

Here is what I have now but getting #Error

=DLookUp("[QtyTransferred]","query2","[OwnerName] = " & "'John Doe ABC'" & " AND [Crop] = [txtCrop2] AND [TransDate] = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "'")
0
Gustav BrockCIOCommented:
Just add it in a similar way:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = 'John Doe ABC' AND Crop = '" & [Reports]![rptInventoryOwnership]![txtCropYear] & "' AND [TransDate] = #" & Format([Reports]![rptInventoryOwnership]![txtCropYear], "yyyy\/mm\/dd") & "#")

Open in new window

If Crop is not text but number, remove the single quotes.
And if year, not date, adjust to my previous code.
0
SteveL13Author Commented:
Frustrating.  Still getting #Error with:

=DLookUp("[QtyTransferred]","query2","[OwnerName] = 'John Doe ABC' AND [Crop] = '" & [Reports]![rptInventoryOwnership]![txtCrop2] & "' AND [TransDate] = " & [Reports]![rptInventoryOwnership]![txtCropYear] & "")

Open in new window


Note:  Crop is text.  CropYear is text.
0
Gustav BrockCIOCommented:
If your TransDate is a date, this will do:
=DLookUp("[QtyTransferred]","query2","[OwnerName] = 'John Doe ABC' AND [Crop] = '" & [Reports]![rptInventoryOwnership]![txtCrop2] & "' AND Year([TransDate]) = " & [Reports]![rptInventoryOwnership]![txtCropYear] & "")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
Are you sure there is a match in the data for the values you are passing?


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.