strSQL with CompletedDate

  • Thread starter Thread starter ChrisP
  • Start date Start date
C

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
today's date. I have no clue how to do this. Any help is appreciated!
Click to expand...

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

SELECT ProjNum, ProjName FROM tblProjectList Where CompletedDate >
Date(Now()) order by ProjNum
 
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 said:
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 said:
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

Thanks,
Chris
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!!!

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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!!!

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Now it's highlighting the words " & dte & ' order by ProjNum" in red and
there is a pop up box saying "Compile error: Syntax error"

:

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.


:

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!!!

:

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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top