Hopefully a simple problem with my report output

[johanvonh]

Some background:


Working on an access database in which data entry people would select a subcategory from a combobox which was being populated from a table containing 2 fields, ID and Category. During the course of using the database, it came up that the people populating the Category field needed to store more than 256 bytes, so the decision was made to switch the Category field type to Memo. Of course Access limits the output in a combobox to 256 bytes, but this was sufficient for data entry people to select the correct item, which then stored the correct record pointer in the main table.

Now that data entry is complete, the issue is arising now (as all of the queries and reports were generated before switching Category to a Memo field) that the reports are only outputting the first 256 characters, obviously problematic for output of the several dozens of items which have more than that.

Things I've experimented with as solutions:
In the original data entry form, I created a textbox next to the combobox with its controlsource set to the following dlookup():
=DLookUp("[Category]","tSubcategory","ID = [SelectedCategory]")
And it works correctly, outputting the entire text of the Category Memo field.

In one of the reports I need to modify, which is generated from a query selecting a subset of the main table, I tried replacing the combobox output (access builds it with an INNER JOIN SQL query, but truncates the output to 256 characters as a result) with a textbox with its controlsource set to the same dlookup() above. Confusingly, it returned blank output. If I manually define the record to lookup, such as:
=DLookUp("[Category]","tSubcategory","ID = 73"), then it resolves and outputs correctly in the report, including the full Memo text. I put a debug text box nearby with the value of [SelectedCategory] on the report, and it prints correctly. I'm at a loss why the dynamic dlookup() isn't evaluating correctly.

I will gladly buy many beers for anyone who can help, and don't care how we achieve it. I Just need the report to correctly print out the complete related Memo field.

Best regards,
Johan

[June7]

Why are you using DLookup? Why not including tSubcategory in the report RecordSource so all related data is available?

[johanvonh]

Why are you using DLookup? Why not including tSubcategory in the report RecordSource so all related data is available?

As I indicated, any solution is fine. What is the best way to relate the fields once I add it to the RecordSource? I assume I'd be adding something like:
SELECT ID, Category
FROM tSubcategory

How do I then accomplish the lookup between ID and SelectedCategory in the report? Can I do it directly with SQL? If so how do I reference it in the report?

[June7]

The report RecordSource would be a query that joins tables so all related data is available. Then bind a textbox to the Category field. The full text of the memo field should display.