Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-22-2017, 09:29 AM
bconner bconner is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Mar 2017
Posts: 2
bconner is on a distinguished road
Default Excel ADO query from Access Not Working

Hello All,


I am running into a problem when I try running the below code in Excel. I am getting message "Syntax error (missing operator) in query expression 'Encounter_Date BETWEEN AND"



Code:
Sub GetProviderIncentiveData()
'Created by Brian Conner on 6/4/2015
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String
Dim PT As PivotTable
Dim WS As Worksheet
 
strBeginDate = Format(Worksheets("Data").Range("B2").Value, "MM/DD/YYYY")
strEndDate = Format(Worksheets("Data").Range("B3").Value, "MM/DD/YYYY")
 
On Error GoTo err_handler

 Set objConn = New ADODB.Connection
 objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\va10vnas003a4\Shared\CRA_Public\Brian_Conner\Databases\Reporting_SharePoint_Databases.accdb;Persist Security Info=False;"
 Set objRS = New ADODB.Recordset
  strSQL = "SELECT * FROM Worklist WHERE Encounter_Date BETWEEN " & strBeginDate & " AND " & strEndDate & "'"
  
  objRS.Open strSQL, objConn
  
 
  
  
  Worksheets("Data").Range("A2").CopyFromRecordset objRS
  objRS.Close
  Set objRS = Nothing
  objConn.Close
  Set objConn = Nothing
  
  
MsgBox "Data Extraction is complete."
 
 
 
 
 Exit Sub
err_handler:
 MsgBox Err.Number & "-" & Err.Description
  objRS.Close
  Set objRS = Nothing
  objConn.Close
  Set objConn = Nothing
  
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query and Power Query not working Excel 2016 bl10 Excel 0 07-22-2016 06:25 PM
Using MS Access Query and VBA to create Excel Chart jrmontgom Excel Programming 0 04-07-2015 11:21 AM
Microsoft Access search macro/query algebrapro18 Misc 1 06-18-2014 04:10 PM
MS Access 2007 query to Merge KenshiroUK Mail Merge 1 02-11-2013 04:31 PM
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel? mag Excel 0 10-18-2012 11:15 AM


All times are GMT -7. The time now is 03:03 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft