Solved

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

Posted on 2014-07-21
13
767 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
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
 
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 84
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

948 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

17 Experts available now in Live!

Get 1:1 Help Now