?
Solved

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

Posted on 2014-07-21
13
Medium Priority
?
781 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 264 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 264 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 261 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 261 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 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