Dlookup criteria is not specified correctly

[CarlyS]

Can someone take a look at this and hopefully spot my error?

=DLookUp("[BxName]","TargetBxLabeledTable",'Tables!TargetBxLabeledTable!StudentFullName =' & [StudentFullName] And 'Tables!TargetBxLabeledTable!BxLabel =' & [Bx1Label])

Right now the field in my report is looking up the first value of BxName in the TargetBxLabeledTable and filling that value in for all report groups regardless of the Bx1Label and StudentFullName values in the report. It appears my criteria is meaningless, but I do not get an error message. I have been messing with this all night.

Thanks to all who take a look.

Carly

 

[WayneRyan]

Carly,

=DLookUp("[BxName]", "TargetBxLabeledTable", "StudentFullName = ' & [StudentFullName] & "' And BxLabel = ' & [Bx1Label] & "'")

Wayne

 

[WayneRyan]

Carly,

The DLookUp might return a Null if there is no match.

You need to either:

SomeVariant = DLookUp(...) <-- A variant CAN be Null

Or

SomeString = Nz(DLookUp(...), "") <-- Assign an empty string if no match

Wayne

 

[CarlyS]

Invalid syntax???

I am getting an invalid syntax message with that...

Too many or too few quotes???

 

[Pat Hartman]

If by "that" you mean "this":

=DLookUp("[BxName]", "TargetBxLabeledTable", "StudentFullName = ' & [StudentFullName] & "' And BxLabel = ' & [Bx1Label] & "'")

There are two missing double quotes:
=DLookUp("[BxName]", "TargetBxLabeledTable", "StudentFullName = '" & [StudentFullName] & "' And BxLabel = '" & [Bx1Label] & "'")

 

[CarlyS]

Yes, that is it! Thank you both so much!

 

[WayneRyan]

Thanks Pat!

On Jury Duty today, got my hands on a computer and lost all control.

Wayne

 

[Pat Hartman]

Happens to me all the time. Thanks to all who correct my typos