?
Solved

Help with the coding of a Macro

Posted on 2007-12-05
4
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 20417160
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
ID: 20418844
Thank you robberbaron, I will try it.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 20419373
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
ID: 20419939
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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 …
Introduction to Processes
Suggested Courses

800 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