ADODB SQL string gives me an error

Question

StartDate3 = Format(StartDate1, "dd/mm/yyyy hh:mm:ss")
EndDate3 = Format(EndDate1, "dd/mm/yyyy hh:mm:ss")

Dim cn As Object
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set sqlConnect = New ADODB.Connection
Set rs = New ADODB.Recordset

sqlConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="SOURCE";Persist Security Info=False;"

cn.Open sqlConnect

rs.ActiveConnection = cn

Sql = "SELECT * FROM [tblDatabase] WHERE [Meldt Dato] BETWEEN '" & StartDate3 & "' AND '" & EndDate3 & "';"

rs.Open Sql, cn, adOpenDynamic, adLockOptimistic

With rs

End With

It gives me an error in Norwegian, so I am not sure what it would say in english, but roughly translated it says "No agreement between datatypes in expression" or something like that :p

is [Meldt Dato] the same data type as StartDate3 and EndDate3? If you're trying to compare an int to a datetime (for example) then you're likely to see this issue — Rich Benner
– Rich Benner, Commented Aug 23, 2016 at 9:14
Do i spy single quotation marks around your date(')? They strike me a bit odd. What I normally use is something like " BETWEEN #01/0/2016 00:05:00# AND #02/02/2016 10:00:50# ", although that might not work with the expression returned by StartDate3 & EndDate3 — Martin Dreher
– Martin Dreher, Commented Aug 23, 2016 at 9:31
Try setting StartDate3 and EndDate3 with CDate to make sure they're of date type — Dave
– Dave, Commented Aug 23, 2016 at 9:33
Looks like it's a MS Access DB so if [Meldt Dato] is of date datatype, begin by surrounding your dates with # instead of ' (quote) — Thomas G
– Thomas G, Commented Aug 23, 2016 at 9:41
I ended up with this: Sql = "SELECT * FROM [tblDatabase] WHERE [Meldt Dato] BETWEEN #" & StartDate1 & " 00:00:00# AND # " & EndDate1 & " 00:00:00#;" And it seems to be working :) Thanks everyone! — Thomas
– Thomas, Commented Aug 23, 2016 at 10:21

KyloRen · Accepted Answer · 2016-08-23 10:30:08Z

1

You don't need this 00:00:00, the hash # tags are the key.

answered Aug 23, 2016 at 10:30

KyloRen

2,7715 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas · Accepted Answer · 2016-08-23 11:46:04Z

0

Changed code to:

Sql = "SELECT * FROM [tblDatabase] WHERE [Meldt Dato] BETWEEN #" & StartDate1 & " # AND # " & EndDate1 & " #;"

And that seemed to do it!

edited Aug 23, 2016 at 11:46

answered Aug 23, 2016 at 10:22

Thomas

3051 gold badge3 silver badges11 bronze badges

1 Comment

KyloRen Over a year ago

You marked your own answer as the solution, when the other answer helped you get there, I don't understand that???

Collectives™ on Stack Overflow

ADODB SQL string gives me an error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related