Date function that converts string doesn't allow for subsequent date range query
Posted on 2014-07-21
Experts generously helped me out with a custom function within Access that converts a string
YYYYMMDD to a date.....
however, when I pull this information into a field of it's own, I can't run regular Date range queries on it.
It seems to work if I want to pull a single date...however, if I want to pull a range, like:
Between [Enter Start Date] And [Enter End Date]
I get odd results..... usually pulling the proper "month range"...but ignoring the date boundary's.
if is ask for >1/1/2013 and <1/31/2013
I'll get dates in years previous, and years after...
I've tried to even do a "make table" and query off a new table, with the dates created from the function, but any date range query, doesn't work properly.
This is one of the original functions I used to create the new date field from the string value.
Public Function GetDateFromYYYYMMDD(ValueIn As String) As Date
Dim s As String
s = Left(ValueIn, 4) & "-" & Mid(ValueIn, 5, 2) & "-" & Right(ValueIn, 2)
GetDateFromYYYYMMDD = CDate(s)