| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
strSQL with CompletedDate
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
Might have to use:
Dim dte As Date dte = Format(Now(), "mm/dd/yyyy") If the Hour:Minutes are an issue. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
strSQL with CompletedDate
I think I know what happened. When you copied the code from here, the
system broke the code into two lines.... This should work. If not, in your VBA make sure that strSQL is one line (omit _ if you are going to place it all in one line). 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: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
