Solved

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

Posted on 2014-07-21
13
762 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
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 84

Accepted Solution

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

Author Comment

by:James Powell
Comment Utility
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 84
Comment Utility
Can you show the full SQL of the query?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Or what it looks like in Design view?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 87 total points
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 84
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for all your responses....we figured out a different way of working around this.

Thanks for your help.
0
 
LVL 84
Comment Utility
Can you post your solution? That would help others who might have the same issue.
0
 
LVL 1

Author Comment

by:James Powell
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now