 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005querying on date ... to/from
I have a date field in a 3rd party database where dates are stored as year, month, day (i.e. May 6, 2005 is 20050506). I have to enter the leading zero on single digit months or days to query (no biggie) BUT ... I can filter from this date forward (>=[Starting Date]), but not from one date to another (>=[Starting Date] And <=[Ending Date]). What am I doing wrong? Surely it's possible to query to/from a date in this format, isn't it? Jackie It's done all the time, both as you've tried (>=, ... <=), and using the expression "Between ... And ...". If your attempt isn't working, what isn't working? Are you getting an error message? Are you getting incorrect results? More info, please... Jeff Boyce <Access MVP> >I have a date field in a 3rd party database where dates are stored as year, > month, day (i.e. May 6, 2005 is 20050506). [quoted text clipped - 8 lines] > this > format, isn't it? When I query only with a starting date, I get everything from that date forward. When I query to/from, I get no error and a blank datasheet. I've never used between/and. I'll try it and see if that makes any difference. > Jackie > [quoted text clipped - 20 lines] > > this > > format, isn't it? between/and didn't work either. > When I query only with a starting date, I get everything from that date > forward. When I query to/from, I get no error and a blank datasheet. I've [quoted text clipped - 24 lines] > > > this > > > format, isn't it? Are your end date months 10,11, or 12? Because they will technically be < 09, 08, etc. if this is a text field. I am assuming that End Date and Start Date are parameters you enter when you run the query. Also why don't you actually convert this field to an actual date field? > between/and didn't work either. > [quoted text clipped - 26 lines] > > > > this > > > > format, isn't it? Ok, here's where I show my ignorance ... how do I convert a field formatted as 20050512 to a date field? > Are your end date months 10,11, or 12? Because they will technically be < > 09, 08, etc. if this is a text field. [quoted text clipped - 34 lines] > > > > > this > > > > > format, isn't it? A couple other thoughts. You may have to set the type of the parameters in the query. And, as implied else-thread, your "dates" may be date/times. What happens if you hard code a date range into your query criteria? Do you get the records you expect? Also, I just re-read what you're doing. If you have a text or number field with 20050505, that is NOT, as far as Access knows, a date. If your query is asking the user to supply a date, you are comparing apples and chainsaws. Good luck Jeff Boyce <Access MVP> > between/and didn't work either. > [quoted text clipped - 5 lines] >> >> > Jackie Got it ... =DateSerial(Left([FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2)). Thanks for your help. > A couple other thoughts. You may have to set the type of the parameters in > the query. [quoted text clipped - 22 lines] > >> > >> > Jackie |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.