How convert SQL code to VBA code

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

SteveL13Asked:
Who is Participating?
 
MacroShadowCommented:
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)) "
0
 
MacroShadowCommented:
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
0
 
SteveL13Author Commented:
When I copy/paste this in the code window, the 3rd line turns red.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Dale FyeCommented:
I've gotta ask, why?  What do you hope to achieve by getting rid of the saved query and using VBA for this purpose?
0
 
SteveL13Author 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?
0
 
SteveL13Author Commented:
MacroShadow...  I just posted another one that is more complicated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.