Return a Value from table when...

[Dragonchaser]

Hi All,

I would like have a control in a form return a value when 3 fields in the form match 3 different fields in a table. The value a want returned is the 4th field in the same record as the 3 matching criteria from the form. I've tried a long dlookup expression with no luck. It always returns the value in the first row.

=DLookUp("[FlangeWidth]","[SingleChamberPipeCalculationData]","[FlangeSize] =" & [Forms]![SingleChamberForm]![MeterRunSize] & And "[Pressure] = " & [Forms]![SingleChamberForm]![PressureRating] & " And [FlangeType]= '" & [Forms]![SingleChamberForm]![UpstreamFitting] & "'")

[MeterRunSize] & [Pressure] are numeric and [UpstreamFitting] is a string.
I've attached a picture as they're worth 1000 words.
Am I using the wrong function all together?

Any light anyone can shed on what I'm doing wrong would be greatly appreciated.

Best Regards,

Mark

 

[RuralGuy]

Your 1st " And " was outside of the quotes.

=DLookUp("[FlangeWidth]","[SingleChamberPipeCalculationData]", _
"[FlangeSize] = " & [Forms]![SingleChamberForm]![MeterRunSize] & _
" And [Pressure] = " & [Forms]![SingleChamberForm]![PressureRating] & _
" And [FlangeType] = '" & [Forms]![SingleChamberForm]![UpstreamFitting] & "'")

 

[Dragonchaser]

Thanks RG.
Fixed the syntax, but now it doesn't return anything.
Will this function only work on new form records?
Or should change all the existing records?

 

[RuralGuy]

DLookup() returns a Null if no match is found.

 

[Dragonchaser]

But it searches all the records in the table, correct?

 

[RuralGuy]

Yes, and only returns the 1st hit.

 

[Dragonchaser]

RG, Thanks so much. Got it working.
Much Appreciated.

 

[RuralGuy]

Glad I could help.