Solved

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

Posted on 2014-07-21
13
777 Views
Last Modified: 2014-07-25
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
Comment
Question by:James Powell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 88 total points
ID: 40209755
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
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 88 total points
ID: 40209769
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
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 87 total points
ID: 40209849
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 1

Author Comment

by:James Powell
ID: 40209863
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
 
LVL 85
ID: 40210078
Can you show the full SQL of the query?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40210454
Or what it looks like in Design view?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 87 total points
ID: 40210529
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40210538
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
 
LVL 85
ID: 40210980
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40218470
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
 
LVL 1

Author Comment

by:James Powell
ID: 40219608
Thank you for all your responses....we figured out a different way of working around this.

Thanks for your help.
0
 
LVL 85
ID: 40219626
Can you post your solution? That would help others who might have the same issue.
0
 
LVL 1

Author Comment

by:James Powell
ID: 40220604
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question