Microsoft Office Forums Excel ADO query from Access Not Working

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-22-2017, 09:29 AM
bconner bconner is offline Excel ADO query from Access Not Working Windows 10 Excel ADO query from Access Not Working Office 2010 32bit
Novice
Excel ADO query from Access Not Working
 
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
Excel ADO query from Access Not Working Microsoft Access search macro/query algebrapro18 Misc 1 06-18-2014 04:10 PM
Excel ADO query from Access Not Working 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 12:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft