DLookUp Associated with Multiple Report Values

[ebadar]

Hello All-

I have two tables that I'm hoping to combine in a report.

tblPlan has the following fields: Company Code, Symbol 1, Symbol 2, Symbol 3... Symbol 30

tblFunds has the following fields: Fund Name, Ticker, Classification, Status

Symbols 1-30 are 5 character stock tickers that match up to certain ones from the field "Ticker" in tblFunds.

I am trying to generate a report that lists Symbols 1-30, grouped by company code, with their corresponding Status and Classification listed next to them. I've tried using a DLookUp in a text box with the following expression:

=DLookUp("Status", "tblFunds", "Ticker =" & [Symbol 1])

But that just returns an Error in the text box.

Any help you guys can offer is much appreciated. I'm not very familiar with VB, but I'm willing to learn, and I have a class on it next semester, so anything to get ahead of the curve on that would be great!

Thanks,
Elliot

 

[pbaldy]

If Ticker is a text field like I assume, the syntax is slightly different:

http://www.mvps.org/access/general/gen0018.htm

 

[ebadar]

Works like a charm! Thank you!!

Now I'm trying to add one to return "Fund Name" and using the following syntax:

=DLookUp("Fund Name","tblFunds","Ticker='" & [Symbol 1] & "'")

And it's returning an #Error

Any advice?

 

[pbaldy]

You may need to bracket Fund Name due to the inadvisable space.

 

[Pat Hartman]

Even better would be to normalize the table so you don't have to run 30 separate queries for each row in the report.