Solved

Help with the coding of a Macro

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 60
Problem to cell option 1 28
Using VBScript. How to obtain the recomended paging file size? 8 47
Sql server insert 13 27
A short article about problems I had with the new location API and permissions in Marshmallow
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

808 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