strSQL VB code in Excel

[ChrisP]

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
is greater than (or equal to) today's date. I have no clue how to do this.
Any help is appreciated!

Thanks,
Chris

 

[JP]

How can a date something was completed be later than today's date?

Otherwise, add a WHERE clause to your SQL statement. For example:

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE
CompletedDate >= GETDATE order by ProjNum"


--JP

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 byProjNum"
    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"

- snip -

 

[ChrisP]

it can and I tried it but I'm getting an error message "Invalid column name
'GETDATE'.=-2147217900/Procedure=Workbook_Open/System=Microsoft OLE DB
Provider for SQL Server" help!!!!

 

[Clif McIrvin]

Try

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE
CompletedDate >= DateValue(Date()) order by ProjNum"

If you have Access, you can open the Access help to the table of
contents and find a section on SQL.

HTH

 

[JP]

It was just a guess. Try the statement in Clif's post and see if that
works.

--JP

 

[Clif McIrvin]

It was just a guess. Try the statement in Clif's post and see if that
works.

And here I thought you knew something I'd not come across before
<grin>.

 

[JP]

I've been reading about SQL lately and took a guess about how to get
the system date. There are several different flavors of SQL (T-SQL,
MySQL, etc) and the OP mentioned "SQL Server" so I googled it and gave
him the syntax for that version.

Hopefully the OP will post back and indicate what worked.

--JP

 

[Clif McIrvin]

I've been reading about SQL lately and took a guess about how to get
the system date. There are several different flavors of SQL (T-SQL,
MySQL, etc) and the OP mentioned "SQL Server" so I googled it and gave
him the syntax for that version.
------
Interesting.

I did a bit of digging in the installed Access help (A2003) and came to
the conclusion that Access knows how to evaluate recognized VBA
functions before it passes the SQL to the backend engine.

Perhaps your syntax would have worked as a "pass-through query" ?

 

[JP]

True, but after looking at your SQL statement (and mine), the VBA
function should be outside the string:

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE
CompletedDate >=" & DateValue(Date()) & "ORDER BY ProjNum"


--JP

 

[Clif McIrvin]

I did wonder about that; simply didn't attempt a test. Thanks for the
follow-up.

--
Clif

True, but after looking at your SQL statement (and mine), the VBA
function should be outside the string:

strSQL = "SELECT ProjNum, ProjName FROM tblProjectList WHERE
CompletedDate >=" & DateValue(Date()) & "ORDER BY ProjNum"


--JP