Unanswered: Using a sub report to join un-related tables
I have 2 tables I need to join them to build a report. But there is no direct relation between the tables. I will have to join substring of a column from 1st table to another column in 2nd table. I read in the help documentation that I can use sub reports to achieve this, but the instructions are not clear.
I played around with the sub reports but couldn't get this to work.
Any suggestions on how to achieve this?
Can I override the SQL query in the report to use my custom join? If yes, how?
You need to create a calculated field in your main report to get the substring that matches the field in your subreport.
Once you have that, you can use it to link the subreport to the main report.
If the two fields are different data types, you'll need to convert the field in the main form to match the field in the subreport. Say Mainfield is a date/time (1/1/2008 11:00:00 AM) and Subfield is a date stored in a text field ("1/1/2008"), then your calculated field has to be a text field as well. You just have to add the conversion to the calculated field (add CStr to the beginning of the calculation)