#Error in a report field

2/9/2018

2/8/2018

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] & "'")

»bp

2/8/2018

ASKER

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] & "'")

2/8/2018

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

»bp

2/8/2018

ASKER

Is a control on the report.

2/8/2018

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] & "'")

»bp

Gustav Brock

2/8/2018

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") & "#")

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

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

2/9/2018

ASKER

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] & "'")

Gustav Brock

2/9/2018

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") & "#")

If Crop is not text but number, remove the single quotes.
And if year, not date, adjust to my previous code.

2/9/2018

ASKER

Frustrating. Still getting #Error with:

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

Note: Crop is text. CropYear is text.

ASKER CERTIFIED SOLUTION

Gustav Brock

2/9/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial