Solved

Help with the coding of a Macro

Posted on 2007-12-05
4
168 Views
Last Modified: 2010-03-20
We have a macro that the coding has to be changed to account for a new table and some information we want to pull from that table and we are having some issues.  The current section of coding we need to change is listed right below.  In the coding, we need to change the projects.ProjNum to Tasks.ProjNum, keep the projects.ProjName and add a Tasks.Description to the Macro.  The Tasks.ProjNum and the Tasks.Description are from a new table so how can we alter the section below to fix this Macro?  Any assistance offered would be greatly appreciated.  

If TextSearch Then
        strSQL = "SELECT projects.ProjNum, projects.ProjName" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects" & Chr(13) & "" & Chr(10) & "WHERE (projects.ProjNum Like '%" & txtSearch & "%') OR (projects.ProjName Like '%" & txtSearch & "%')" & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY projects.ProjNum"
    Else
        If optDateSearch.Value = True And optAfter.Value = True Then
            strSQL = "SELECT projects.ProjNum, projects.ProjName" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects" & Chr(13) & "" & Chr(10) & "WHERE (projects.date_assigned >= '" & txtDate & "') " & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY projects.ProjNum"
        ElseIf optDateSearch.Value = True And optBefore.Value = True Then
            strSQL = "SELECT projects.ProjNum, projects.ProjName" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects" & Chr(13) & "" & Chr(10) & "WHERE (projects.date_assigned <= '" & txtDate & "') " & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY projects.ProjNum"
        End If
    End If

Open in new window

0
Comment
Question by:regsamp
  • 2
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
Comment Utility
change
FROM TEST.dbo.projects projects
to
FROM TEST.dbo.projects projects. TEST.dbo.tasks Tasks


If TextSearch Then

        strSQL = "SELECT Tasks.ProjNum, projects.ProjName, Tasks.Description" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects,TEST.dbo.tasks tasks " & Chr(13) & "" & Chr(10) & "WHERE (Tasks.ProjNum Like '%" & txtSearch & "%') OR (projects.ProjName Like '%" & txtSearch & "%')" & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY tasks.ProjNum"

    Else

        If optDateSearch.Value = True And optAfter.Value = True Then

            strSQL = "SELECT Tasks.ProjNum, projects.ProjName, Tasks.Description" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects,TEST.dbo.tasks tasks " & Chr(13) & "" & Chr(10) & "WHERE (projects.date_assigned >= '" & txtDate & "') " & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY Tasks.ProjNum"

        ElseIf optDateSearch.Value = True And optBefore.Value = True Then

            strSQL = "SELECT Tasks.ProjNum, projects.ProjName, Tasks.Description" & Chr(13) & "" & Chr(10) & "FROM TEST.dbo.projects projects,TEST.dbo.tasks tasks " & Chr(13) & "" & Chr(10) & "WHERE (projects.date_assigned <= '" & txtDate & "') " & Chr(13) & "" & Chr(10) & " AND (projects.Status = 'A')" & Chr(13) & "" & Chr(10) & " ORDER BY Tasks.ProjNum"

        End If

    End If

Open in new window

0
 

Author Comment

by:regsamp
Comment Utility
Thank you robberbaron, I will try it.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
you dont need the cr & lf in the SQL though it makes it easier to read debug.

and use the builtin constant  vbCrLf as in
.... & txtDate & "') " & vbCrLf & " AND .....

if you really want the sql to be easy to read.
0
 

Author Comment

by:regsamp
Comment Utility
I tried the coding and it is given me output but the data is looking off so I guess I will have to debug some more.  Thank you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now