Refresh Query based on variable

G

GregR

I want to refresh a query based on a variable. I have the following
which does not work.

Sub AddDetailSheet()

Dim Proj As Variant
Proj = InputBox("Enter Project Code, as 00-000-000-000-000", "Project
Code")

Range("G2").Select
With Selection.QueryTable
.Connection = Array(Array( _

"ODBC;DSN=Clockworks;Description=Clockworks;UID=sa;APP=Microsoft Office
2000;WSID=ERIKEWING;DATABASE=Intranet;LANGUAGE=us_english;Net" _
),
Array("work=DBMSSOCN;Address=172.30.1.110,1433;Trusted_Connection=Yes"))
.CommandText = Array( _
"SELECT tbl_TimeSlips1.Year, tbl_TimeSlips1.Month,
tbl_TimeSlips1.Week, tbl_ISProjects.Description, tbl_ISPersonnel.Name,
tbl_ISPersonnel.Department, tbl_ISProjects.GLCode,
tbl_TimeSlips1.Hours, tbl_IS" _
, _
"Personnel.Rate, tbl_TimeSlips1.Notes,
tbl_ISProjects.Capitalized" & Chr(13) & "" & Chr(10) & "FROM
Intranet.dbo.tbl_ISPersonnel tbl_ISPersonnel,
Intranet.dbo.tbl_ISProjects tbl_ISProjects, Intranet.dbo.tbl_TimeSlips1
tbl_TimeSli" _
, _
"ps1" & Chr(13) & "" & Chr(10) & "WHERE
tbl_TimeSlips1.ContractorCode = tbl_ISPersonnel.RecordID AND
tbl_TimeSlips1.GL = tbl_ISProjects.RecordID AND
((tbl_TimeSlips1.Year=2006 Or tbl_TimeSlips1.Year=2005) AND
(tbl_TimeSlips1.Hour" _
, _
"s>0) AND (tbl_ISProjects.Capitalized In (0,1,3)) AND
(tbl_ISPersonnel.Status In ('Associate','Contractor')) AND
(tbl_ISProjects.GLCode In (Proj)))" & Chr(13) & "" & Chr(10) & "ORDER
BY tbl_ISProjects.Description," _
, " tbl_ISPersonnel.Name")
.Refresh BackgroundQuery:=False
End With

End Sub

Any help would be appreciated. TIA

Greg
 
G

GregR

The bottom line is I want this part of the code
(tbl_ISProjects.GLCode In (Proj)))" Proj = to equal the value in the
input box or more simpler the value in "G2". Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Line Wrap Problem 1
Edit Query 0

Top