Moritz Moritz - 27 days ago 11
SQL Question

SQL Query from Excel via VBA to Access (date specific)

Good morning,

for a testing purpose I try to get some data from an Access table to a recordset in Excel using vba and SQL (Direction: Excel + VBA to Access). The Access table is called "T_Zeiten" and one coloumn is called "zeiDat" which contains dates (european style like 09.11.2016).
What I want to do is opening a connection, look for all entries between two specific dates and write them to a recordset. I already managed to open a connection but struggle with the date stuff (US-style dates). The "From" and "To" dates in Excel are also formatted as "dd.mm.yyyy".

This is what I have so far:

Dim cn As Object
Dim ZeitenArbeitenGrob As Recordset
Dim strSqlZeitenArbeitenGrob As String
Dim start As String
Dim ende As String
start = Application.WorksheetFunction.Text(Cells(3, 2), "dd/mm/yyyy")
ende = Application.WorksheetFunction.Text(Cells(4, 2), "dd/mm/yyyy")
Dim strConnection As String
celllocation = 6
Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Users\mj\Downloads\Neuer Ordner (2)\ZeitErfKonst.mdb"
strSqlZeitenArbeitenGrob = "SELECT COUNT(zeiDat) FROM T_Zeiten WHERE [zeiDat] BETWEEN #" & start & "# And #" & ende & "#"
cn.Open strConnection
Set ZeitenArbeitenGrob = cn.Execute(strSqlZeitenArbeitenGrob)
MsgBox ZeitenArbeitenGrob.Fields(0)


There are 15 entries but the messagebox shows me 213 and I don't know why.

Answer

You need to use an unambiguous format for the string expressions of the date values:

start = Application.WorksheetFunction.Text(Cells(3, 2), "yyyy/mm/dd")
ende = Application.WorksheetFunction.Text(Cells(4, 2), "yyyy/mm/dd")
Comments