Solved

Help with the coding of a Macro

Posted on 2007-12-05
4
176 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 500 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

717 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