• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

Date function that converts string doesn't allow for subsequent date range query

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)

End Function
0
James Powell
Asked:
James Powell
  • 4
  • 3
  • 2
  • +4
4 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
when I pull this information into a field of it's own,
What do you mean by that? Are using the custom function to insert data into a new field in a table? If so, what Data type is that field?

FWIW, I generally use > and < logic instead of Between:

WHERE MyDateField >#01-01-2010# AND MyDateField <#02-01-2014$
0
 
Randy PooleCommented:
Also your cdate may not be interpreting YYYY-MM-DD as you may think, have stepped through this code or put up a msgbox to see if the return value is what your expecting?
0
 
Russell FoxDatabase DeveloperCommented:
FYI, if you're querying in SQL Server 2008+, YYYYMMDD is one of the few strings that SQL handles really well, so you could convert that column on the fly. It looks like you are asking the user to supply start/end dates - are you sure those values are coming in an expected format?
SELECT *
FROM MyTable
WHERE CAST(myDate AS DATE) > CAST(inputDate1 AS DATE) 
    AND CAST(myDate AS DATE) < CAST(inputDate2 AS DATE)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
I think it is the second response that relates to what is going on....

I'm populating a new field within the Access query, by simply using the function following Expr1:

But a data type isn't specified, as it is a new field.

I also, normally use the >  and <  syntax also, but that totally blew up the query, and altered what the Expr1 formula contained,  placed the < value, in an entirely new field that I hadn't created.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the full SQL of the query?
0
 
Helen FeddemaCommented:
Or what it looks like in Design view?
0
 
PortletPaulCommented:
FWIW, I generally use > and < logic instead of Between:

WHERE MyDateField >#01-01-2010# AND MyDateField <#02-01-2014$

I hope it is actually >= then < 
that you use, e.g.

WHERE MyDateField >#01-01-2014# AND MyDateField <#02-01-2014$

assuming 02-01-2014 is February that where condition would give you absolutely everything in Jan 2014

(ps: my native date sequence is dd/mm/yyyy
so when you guys write 02-01-yyyy or 07-11-yyyy I have no idea what you are really trying to convey
doesn't Access support a more internationlized date?)
0
 
PortletPaulCommented:
Have you tried YYYYMMDD in the date range query?

select
*
from YourTable
where [date field here] >= GetDateFromYYYYMMDD('20100101')
and [date field here] < GetDateFromYYYYMMDD('20140201')

or perhaps:

select
*
from YourTable
where GetDateFromYYYYMMDD([string yyyymmdd field here]) >= CDate('2010-01-01')
and GetDateFromYYYYMMDD([string yyyymmdd field here]) < CDate('2014-02-01')
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I hope it is actually >= then < 
that you use, e.g.
Depending on what I'm after, I use whatever is needed. If I want to include the first date in the range, then I use >=. If I do NOT want to include the beginning date in the range, then I use >. This has gotten me into trouble with DateTime fields, so I've had to be careful which way I went with it, since anytime AFTER midnight of a date is "greater than" that date.

(ps: my native date sequence is dd/mm/yyyy
so when you guys write 02-01-yyyy or 07-11-yyyy I have no idea what you are really trying to convey
doesn't Access support a more internationlized date?
In almost every case, Access expects dates to be in "American" format: mm\dd\yyyy. Providing dates to Queries, VBA code, etc in other formats will cause issues. There are other versions of Access which may handle this differently, but in the US, using anything other than mm\dd\yyyy can cause Access to behave oddly.

See Allen Browne's article: http://allenbrowne.com/ser-36.html
0
 
Anthony PerkinsCommented:
I get the impression the author has inadvertently included MS SQL Server, as they are using MS Access, not knowing the SQL syntax and database are different.
0
 
James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
Thank you for all your responses....we figured out a different way of working around this.

Thanks for your help.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post your solution? That would help others who might have the same issue.
0
 
James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
Hi Scott...

It was really just a "work around".

We ended up just taking a "fuzzy match" range of  the year that we wanted... 2013*

Not really a fix, but that was what we settled for....sorry I couldn't contribute more.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now