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