asked on 2/26/2018

Convert complicated SQL codes together to VBA to be used as record source for a report

I am using two queries like:

SELECT tblCategoriesAndSubCategories.ElevatorReportSortOrder, tblCategoryInfo.Category, tblCategoryInfo.SubCategory, tblCategoryInfo.Description, tblCategoryInfo.JobNo
FROM tblCategoryInfo INNER JOIN tblCategoriesAndSubCategories ON (tblCategoryInfo.Category = tblCategoriesAndSubCategories.Category) AND (tblCategoryInfo.SubCategory = tblCategoriesAndSubCategories.SubCategory)
WHERE (((tblCategoryInfo.Description) Not Like "NA") AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob2]) AND ((tblCategoryInfo.ElevatorReport)=True)) OR (((tblCategoryInfo.Description) Is Null) AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob2]) AND ((tblCategoryInfo.ElevatorReport)=True))
ORDER BY tblCategoriesAndSubCategories.ElevatorReportSortOrder;

Open in new window

And

SELECT tblCategoriesAndSubCategories.ElevatorReportSortOrder, tblCategoryInfo.Category, tblCategoryInfo.SubCategory, tblCategoryInfo.Description, tblCategoryInfo.JobNo
FROM tblCategoryInfo INNER JOIN tblCategoriesAndSubCategories ON (tblCategoryInfo.Category = tblCategoriesAndSubCategories.Category) AND (tblCategoryInfo.SubCategory = tblCategoriesAndSubCategories.SubCategory)
WHERE (((tblCategoryInfo.Description) Not Like "NA") AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob3]) AND ((tblCategoryInfo.ElevatorReport)=True)) OR (((tblCategoryInfo.Description) Is Null) AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob3]) AND ((tblCategoryInfo.ElevatorReport)=True))
ORDER BY tblCategoriesAndSubCategories.ElevatorReportSortOrder;

Open in new window

and then am combining them together in a final query with:

SELECT qryElevatorReportDetailColumn1.ElevatorReportSortOrder AS SortOrder, qryElevatorReportDetailColumn1.SubCategory AS Job1SubCategory, qryElevatorReportDetailColumn1.Description AS Job1Description, qryElevatorReportDetailColumn1.JobNo AS Job1JobN, qryElevatorReportDetailColumn2.SubCategory AS Job2SubCategory, qryElevatorReportDetailColumn2.Description AS Job2Description, qryElevatorReportDetailColumn2.JobNo AS Job2JobN
FROM qryElevatorReportDetailColumn1 INNER JOIN qryElevatorReportDetailColumn2 ON qryElevatorReportDetailColumn1.SubCategory = qryElevatorReportDetailColumn2.SubCategory;

Open in new window

How can I convert all of this to VBA as the record source for the report?

Microsoft Access

Last Comment

mbizup

2/27/2018

aikimark

2/26/2018

paste the combined query into the recordsource for the report

SteveL13

2/26/2018

ASKER

I probably didn't explain this well. I want to convert all 3 SQL queries to VBA code. If you refer to another topic I posted earlier today, How convert SQL code to VBA code, you will see how another expert helped me out.

aikimark

2/26/2018

These two languages solve two very different problems. I don't see how or why you need a conversion. I'll watch what other experts post.

PatHartman

2/26/2018

How can I convert all of this to VBA as the record source for the report?

The RecordSource for a form or report is NOT VBA. It is a Table name, a querydef name, or an SQL String

Steve, if the third query works when you run it with the QBE (open the QBE, close the dialog, switch to SQL View, paste the SQL string and run it), then just paste the string into the Form or Report's RecordSource.

However, saved querydefs (follow the steps above, save the query and give it a menaingful name) are slightly more efficient. In that case, you would paste the query name into the RecordSource rather than the SQL string.

PS - you shouldn't have two questions on the same topic open and if you want people to look at a different question, please post a link.