Solved

Help with the coding of a Macro

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 …
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 …

862 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

25 Experts available now in Live!

Get 1:1 Help Now