#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?
 
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
 
Bill PrewCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
What is txtCrop2?  A VBA variable?  A table column?  A control on the form?


»bp
0
 
SteveL13Author Commented:
Is a control on the report.
0
 
Bill PrewCommented:
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
 
Bill PrewCommented:
Are you sure there is a match in the data for the values you are passing?


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.