Excel VBA SQL Query to Access to return records between dates

Question

Morning all,

I am hoping this is a quick one. I have a query in Excel that requests records between two date points. It returns without errors but also returns unexpected values, seemingly because the date has been swapped around. I am working with the UK date format (dd/mm/yyy).

I have tried passing the date as a date type and a string type, as a datevalue and just the text. I have also tried passing it as yyyymmdd.

In the below example sFromDate is 06/04/2016 (6th April) and sToDate is 12/04/16 (12th April). Dates between June and December are being returned.

sSelectedArea = "ThisTMRacf"
sFromDate = DateValue(Sht_SpecificView.Range("D5"))
sToDate = DateValue(Sht_SpecificView.Range("F5"))

Set rex = db.OpenRecordset("SELECT eGain.RecordDate FROM [eGain], [Staff] 
WHERE eGain.Racf = Staff.Racf AND (Staff.TeamManager = '" & sSelectedArea & "' 
OR Staff.CSM = '" & sSelectedArea & "') 
AND eGain.RecordDate BETWEEN #" & sFromDate & "# AND #" & sToDate & "# ;")

Is there another way to pass the date so that it doesn't convert?

As an aside, old-style joins are never pretty and could bite you in the bottom further down the line — JohnHC
– JohnHC, Commented Apr 12, 2017 at 10:35
Appreciate the feedback, John. How would you restructure it? — Snayff
– Snayff, Commented Apr 12, 2017 at 11:15
FROM [eGain] inner join [Staff] on eGain.Racf = Staff.Racf WHERE... — JohnHC
– JohnHC, Commented Apr 12, 2017 at 11:47

Snayff · Accepted Answer · 2017-04-12 10:24:46Z

2

Formatting the dates as yyyy/mm/dd worked. Example below.

sFromDate = Format(Sht_Dashboard.Range("D5"), "yyyy/mm/dd")
sToDate = Format(Sht_Dashboard.Range("F5"), "yyyy/mm/dd")

answered Apr 12, 2017 at 10:24

Snayff

9711 bronze badges

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

Comments

JohnHC · Accepted Answer · 2017-04-12 09:44:14Z

1

Might have to be ruthless with this and break the dates down and rebuild...

sFromDateY = Year(DateValue(Sht_SpecificView.Range("D5")))
sFromDateM = Month(DateValue(Sht_SpecificView.Range("D5")))
sFromDateD = Day(DateValue(Sht_SpecificView.Range("D5")))

Then use

AND eGain.RecordDate BETWEEN DateSerial(" & sFromDateY &", "& sFromDateM &", "&sFromDateD &") ...

answered Apr 12, 2017 at 9:44

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

2 Comments

Snayff Over a year ago

If it works I don't mind a bit of ruthlessness! Will try shortly and report back.

Snayff Over a year ago

Thanks for the suggestion, John. I managed to utilise a simpler approach. I have posted the answer.

Collectives™ on Stack Overflow

Excel VBA SQL Query to Access to return records between dates

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related