SQL statement question

Someone please help with this coding. I'm getting a data mismatch
error.

Dim cn As ADODB.Connection
Dim str As String
Dim sql As String
Dim lRecords As Long
Dim rs As ADODB.Recordset
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim pNum As String
Dim cl As String
Dim oldP As Date

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets("Holding")
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


' connect to the Access database
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits\Permit.Tracker
\Database\Permit.Tracker.mdb;"



str = "SELECT Min(permit_log_date) AS Minofpermit_log_date FROM
permit_info WHERE curr_location '" & cl & "'" 'original working
line
'str = "SELECT permit_num AS Minofpermit_log_date FROM permit_info"
'testing line
rs.Open str, cn ', adOpenKeyset ', adLockOptimistic
With wsSheet1
.Cells(3, 6).CopyFromRecordset rs
End With
oldP = Sheets("Holding").Range("F3").Value
rs.Close
str = "SELECT permit_num FROM permit_info WHERE permit_log_date = '" &
oldP & "'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic 'GETTING ERROR ON
THIS LINE
'Sheets("Report.Card").Range("G7").CopyFromRecords et rs
Sheets("Holding").Range("A3").CopyFromRecordset rs

Fin:
ActiveWorkbook.Sheets("Report.Card").Protect
Application.ScreenUpdating = True
End Sub

Try this
#" & oldP & "#"

"gab1972" wrote:

Someone please help with this coding. I'm getting a data mismatch
error.

Dim cn As ADODB.Connection
Dim str As String
Dim sql As String
Dim lRecords As Long
Dim rs As ADODB.Recordset
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim pNum As String
Dim cl As String
Dim oldP As Date

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets("Holding")
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


' connect to the Access database
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits\Permit.Tracker
\Database\Permit.Tracker.mdb;"



str = "SELECT Min(permit_log_date) AS Minofpermit_log_date FROM
permit_info WHERE curr_location '" & cl & "'" 'original working
line
'str = "SELECT permit_num AS Minofpermit_log_date FROM permit_info"
'testing line
rs.Open str, cn ', adOpenKeyset ', adLockOptimistic
With wsSheet1
.Cells(3, 6).CopyFromRecordset rs
End With
oldP = Sheets("Holding").Range("F3").Value
rs.Close
str = "SELECT permit_num FROM permit_info WHERE permit_log_date = '" &
oldP & "'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic 'GETTING ERROR ON
THIS LINE
'Sheets("Report.Card").Range("G7").CopyFromRecords et rs
Sheets("Holding").Range("A3").CopyFromRecordset rs

Fin:
ActiveWorkbook.Sheets("Report.Card").Protect
Application.ScreenUpdating = True
End Sub
.

On Nov 19, 11:38*am, Mike wrote:
Try this
#" & oldP & "#"



"gab1972" wrote:
Someone please help with this coding. *I'm getting a data mismatch
error.

Dim cn As ADODB.Connection
Dim str As String
Dim sql As String
Dim lRecords As Long
Dim rs As ADODB.Recordset
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim pNum As String
Dim cl As String
Dim oldP As Date

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets("Holding")
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' connect to the Access database
'Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits\Permit.Tracker
\Database\Permit.Tracker.mdb;"

str = "SELECT Min(permit_log_date) AS Minofpermit_log_date FROM
permit_info WHERE curr_location '" & cl & "'" 'original working
line
'str = "SELECT permit_num AS Minofpermit_log_date FROM permit_info"
'testing line
rs.Open str, cn ', adOpenKeyset ', adLockOptimistic
With wsSheet1
* * * * .Cells(3, 6).CopyFromRecordset rs
End With
oldP = Sheets("Holding").Range("F3").Value
rs.Close
str = "SELECT permit_num FROM permit_info WHERE permit_log_date = '" &
oldP & "'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic *'GETTING ERROR ON
THIS LINE
'Sheets("Report.Card").Range("G7").CopyFromRecords et rs
Sheets("Holding").Range("A3").CopyFromRecordset rs

Fin:
ActiveWorkbook.Sheets("Report.Card").Protect
Application.ScreenUpdating = True
End Sub
.

THANK YOU THANK YOU THANK YOU THANK YOU!!! That worked. I NEVER
would have guessed something like that. Thanks for all your help.