We help IT Professionals succeed at work.

How convert SQL code to VBA code

SteveL13
SteveL13 asked
on
High Priority
110 Views
Last Modified: 2018-02-26
I have the following SQL code in query designer.  The query becomes the record source for a report.  But I'd like to convert it to VBA code and use that VBA code as the record source for the report and don't know where to begin.



SELECT tblCategoriesAndSubCategories.OwnerReportSortOrder, 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]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True)) OR (((tblCategoryInfo.Description) Is Null) AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True))
ORDER BY tblCategoriesAndSubCategories.OwnerReportSortOrder;

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
strSQL = "SELECT tblCategoriesAndSubCategories.OwnerReportSortOrder, tblCategoryInfo.Category, tblCategoryInfo.SubCategory, tblCategoryInfo.Description, tblCategoryInfo.JobNo "
strSQL = strSQL & "FROM tblCategoryInfo INNER JOIN tblCategoriesAndSubCategories ON (tblCategoryInfo.Category = tblCategoriesAndSubCategories.Category) AND (tblCategoryInfo.SubCategory = tblCategoriesAndSubCategories.SubCategory) "
strSQL = strSQL & "WHERE (((tblCategoryInfo.Description) Not Like """NA""") AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True)) OR (((tblCategoryInfo.Description) Is Null) AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True))"
strSQL = strSQL & "ORDER BY tblCategoriesAndSubCategories.OwnerReportSortOrder;"

Me.RecordSource = strSQL

Author

Commented:
When I copy/paste this in the code window, the 3rd line turns red.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I've gotta ask, why?  What do you hope to achieve by getting rid of the saved query and using VBA for this purpose?

Author

Commented:
The issue is this...   The existing query is currently the record source for just one sub-report.  But on the main report there will be a total if 6 sub-reports which are either visible or not visible depending on a select report on a form that has a command button that opens the main report.  I just thought that the VBA code would be cleaner than having 6 queries.  And for now there are just 6 possibilities.  There may be more before I'm done.  I'd control the record source "selection" via if/then code surrounding the SQL code.  Make sense?
CERTIFIED EXPERT
Commented:
Replace that line with this:
strSql = strSql & "WHERE (((tblCategoryInfo.Description) Not Like " & """NA""" & ") AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True)) OR (((tblCategoryInfo.Description) Is Null) AND ((tblCategoryInfo.JobNo)=[Forms]![frmReportSelector]![cboSelectJob]) AND ((tblCategoryInfo.OwnerReport)=True)) "

Author

Commented:
MacroShadow...  I just posted another one that is more complicated.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.