No problem whats-so-ever... you're helping me so no complaining here!!!

so the srtsql did have a dim, I change the dte so now I have:


Dim dte As String
dte = "'" & Format(Now(), "mm/dd/yyyy") & "'"

'Load the list of project numbers and names in the Lookups sheet
strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE
CompletedDate "
" & dte & " order by ProjNum"

but I'm still getting a syntax error on that last line.

"Thomas [PBD]" wrote:

Chris,

This is harder than I thought without being able to access the table, I cant
test the script beforehand. Bare with me now.

Dim dte As String
dte = "'" & Format(Now(), "mm/dd/yyyy") & "'"

Is there a Dim for the strSQL? If not, might need to add:
Dim strSQL as String

Replace the Dim and dte now. There shouldn't be an issue with the Syntax on
the String for the SQL.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"ChrisP" wrote:

Now it's highlighting the words " & dte & ' order by ProjNum" in red and
there is a pop up box saying "Compile error: Syntax error"

"Thomas [PBD]" wrote:

Well... Let's try placing a variable on it and see if this works. Place the
Dim and dte lines above the strSQL and completely replace the strSQL string.

Dim dte as Date
dte = Now()

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE CompletedDate
" & dte & " order by ProjNum"

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"ChrisP" wrote:

I tried this but I'm getting an error " 'Now' is not a recognized function
name. = -2147217900/Procedure = Workbook_Open/System=Microsoft OLE DB
Provider for SQL Server".... help!!!

"Thomas [PBD]" wrote:

I believe changing this in the strSQL will fix your issue.

SELECT ProjNum, ProjName FROM tblProjectList Where CompletedDate
Date(Now()) order by ProjNum


--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.


"ChrisP" wrote:

I'm relatively new to VB so please bare with me...

I have a spreadsheet that has VB that pulls information from a SQL server.
Here is the part of the code I think we need to fix:

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList order by ProjNum"
Set recProjList = New ADODB.Recordset
recProjList.CursorLocation = adUseClient
recProjList.CursorType = adOpenStatic

recProjList.Open strSQL, connTMSDB

Worksheets("Project Numbers").Activate
ActiveSheet.Unprotect "locked"

Cells.Select
Selection.Delete

Cells(1, 1).Value = "Project #"
Cells(1, 2).Value = "Project Name"

Columns("A:A").ColumnWidth = 15
Columns("B:B").ColumnWidth = 54
Range("A1:B1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

recProjList.MoveFirst
intRow = 2
Do While Not recProjList.EOF
Cells(intRow, 1).Value = recProjList!ProjNum
Cells(intRow, 2).Value = recProjList!ProjName
recProjList.MoveNext
intRow = intRow + 1
Loop
ActiveSheet.Protect "locked", DrawingObjects:=True, Contents:=True,
Scenarios:=True
Worksheets("Timesheet").Activate

The table tblProjectList has a column called CompletedDate and I would like
the SQL select statement to only bring back the items where the completeddate
today's date. I have no clue how to do this. Any help is appreciated!

Thanks,
Chris