Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Search (Access97)

    I got a report from a user who found a query with a between...and date range was omitting the final date. Sure enough, I look at the query, a simple one, and whatever range a put in it includes everything except the final date. Has anyone ever come across this? I can think of no reason this would happen. That final date should be included.

    Thanks,

    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access97)

    I've seen it when the dates are general dates (i.e., they include a time). If the ending date is entered as something like 01/07/03, then you will get every date up to 01/07/03 00:00.00, or midnight, which is when 1/7/03 starts. To get the ending date, you either have to enter the next day or skip the "between ... and" and use a >= [start date] and < ([End Date] + 1).
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Date Search (Access97)

    Might the dates have a time attached to them?
    Dates and times are just numbers with the time bit coming after the dec point.

    So Jan 8 2003 1 pm > Jan 8 2003

    So a query that looks for dates between jan 1 2003 and jan 8 2003 will not return a date stored as jan 8 2003 1pm.
    Regards
    John



  4. #4
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    Thanks for the replies. After much digging, it turned out to be exactly what both of you suggested. While all views of the date were set to ShortDate, the underlying data included time. Apparently the engine decides that anything after midnight doesn't fulfill the between function. Kind of weird, but at least it makes sense.

    The real problem is one I posted a couple of weeks ago. I have several copies of this database running for several clients over a wide area. In only two cases have I found that =date() for a default value simply will not work. It shows up in the field in the form as ?number. But when I change it to =Now(), it works fine. Unfortunately, =Now() returns a time with the date. I can't figure out why this would be the case. Some of the other installations are Access 97 and work fine. The ones that are 2000 or 2002 also work fine. I have never been able to find out why this glitch is there.

    At this point I removed all the default values and corrected the entries to remove the time so all queries now function correctly. Anyone with any insight on the root problem, I'd love to hear from.

    thanks again,

    Dave

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Search (Access97)

    Could it be missing references?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access97)

    Take a look at this MSKB article and see if that helps. It describes exactly this problem. You may not have a reference that shows up as MISSING, but it can still be a reference problem and the article shows how to refresh the references as well.
    Charlotte

  7. #7
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    The MS article does describe the problem exactly. This database was developed on my computer with Access 2000 and placed on systems with Access 97. I've run into other problems with the continual conversions at this site. In fact, in the site mentioned I have stopped doing this conversion and only work on the live database via PCAnywhere on their system. It's a bit slower, but avoids this problem.

    As to the solution, it did not work. I have actually seen this before. Occassionally when you convert from 97 to 2000 and back you will get a warning about VB Code not being the correct version. The pop up warning from Access actually suggests doing the fix listed in this article, although I've never done it up until now. I did do it this morning, but it didn't change anything.

    The really odd thing to me is that =Now() works, but =Date() still results in #Number?. You'd think that if it's a reference problem it would be missing both. In fact this article implied as much. By the way, once you begin entering data in the record, today's date does pop up in the field by itself.

    This continues to be a weird one.

    Thanks for all the help.

    Dave

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access97)

    Now and Date are from different libraries, if I remember correctly. Now() almost always works in my experience. It's Date and some of the others that misbehave in this fashion. What happens if you create a new database in Access 97 and import all the objects from your converted database? And which DAO reference is set in your A97 database, DAO 3.51 or DAO 3.6?
    Charlotte

  9. #9
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    Good suggestion about a new version with all items imported. It will be tonight before I can get to the system, but I'll try that. I'll also check to see which version of DAO is running and get back to you on that. This particular site is scheduled to be upgraded to Windows 2000 and Office 2000 or 2002 in the next couple of months. I would hope that will fix it regardless, although I am a bit worried about the initial conversion since I've had the problems.

    Thanks again for the help.

    Dave

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Search (Access97)

    Nothing to do with the problem at hand, but when you respond to someone's post don't keep doing it to your original post but respond to theirs instead. This way they will get an email that they can then respond to. It's almost like a reminder.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    You are my new favorite person in the world. The new database/import took care of the problem. You know I remember doing this once a few years ago when I had a corrupted object that I could not delete, but I had forgotten the option.

    Thanks for all the help. If you need a tote bag, let me know!!

    Thanks,

    Dave

  12. #12
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    BTW. The DAO version is 3.5.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access97)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> The DAO reference for Access 97 should be DAO 3.51, not 3.5 unless you're never applied any Office 97 service releases or Jet service packs. What version of Jet are you using? There have been something like 6 Jet service packs, and you should get the latest for Jet 3.5x and apply it to all machines that run the application. Mixing versions across machines will result in some extremely weird behavior.
    Charlotte

  14. #14
    Lounger
    Join Date
    Dec 2002
    Location
    Hutchinson, Kansas, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Search (Access97)

    This isn't my facility, it it belongs to a client. I have no control of their IP functions. But it is good to know about the upgrades. They are scheduled to upgrade this guy's machine to 2000 soon. That should help greatly. By the way, I was reading someone's comments about 2002. I must agree. I see little reason to make that move. We have not upgraded to Office 2002 in my real job, and probably won't for some time.

    Thanks again for all the help. It is deeply appreciated.

    Dave

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Search (Access97)

    I've used Office 2000 for years, but I'm running OXP at work and like it. I don't see any reason to use the 2002 Access format, though, even in AXP. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •