Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

#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] & "#")
Avatar of Bill Prew
Bill Prew
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
Avatar of SteveL13
SteveL13
Flag of United States of America image

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] & "'")
Avatar of Bill Prew
Bill Prew
What is txtCrop2?  A VBA variable?  A table column?  A control on the form?

»bp
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

Is a control on the report.
Avatar of Bill Prew
Bill Prew
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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
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

Avatar of SteveL13
SteveL13
Flag of United States of America image

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] & "'")
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
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.
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bill Prew
Bill Prew
Are you sure there is a match in the data for the values you are passing?

»bp