If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
SQL statement question
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 . |
#3
|
|||
|
|||
SQL statement question
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. |
Thread Tools | |
Display Modes | |
|
|