dcsimg
Results 1 to 10 of 10

Thread: May Day

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,238

    May Day

    Does anybody know of any reason why an Access mdb might have peculiar behavior related to the date 5/10/2018?

    I recently got called in for an issue with a program I wrote about 20 years back in VB6. The source code is long gone, largely because it was stolen from my house, the backups were dated, and I took the opportunity to walk away from what I would call a fairly amusing mess (it wasn't truly dreadful, and there were even some good parts, but it was a product of the times, and a mess). Since I can't examine the source code, and can barely remember what it does, all I could do was mess around with the behavior.

    In the program, I had inadvertently re-invented part of SQL (there's a long and irrelevant story behind that). The result was that a field in one table contained three elements that made up the WHERE clause of a query against a second table. Two of those elements were strings that the user picked from a table (they never entered them by hand), and the third element was a date. Just to be sure, I copied a known good entry from an earlier record into this field. That known good entry had the date 5/9/2018.

    In the second table, two records had the date 5/10/2018. If I changed those dates to 5/9/2018, such that it matched the date from the WHERE clause, the two records were returned. This tells me that the two string fields, which were also part of the WHERE clause were fine. However, when I changed the date in the WHERE clause to 5/10/2018, and changed the date in the two records to 5/10/2018, the records were not returned. I changed back and forth between 5/9 and 5/10 a few times to be sure that I hadn't simply made a typo (or two), but the behavior was consistent.

    So, I figured I'd ask on here in case 5/10/2018 had some kind of arcane significance in MS Access mdb files. I don't expect it to, but I can't imagine what I could have put into the code that would make a query fail for this one date. After all, the code has been in continual use for some 20 years. While not every date has been encountered in that time....a whole lot of them have.

    Any suggestions?
    My usual boring signature: Nothing

  2. #2
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,122

    Re: May Day

    Hi,

    if this is a Access 97 mdb, then yes the act quit funny after a period of time.
    I had back in 2016 problems with a field with prefix:
    Mr
    Mrs
    Dr.
    Prof. Dr
    etc..
    every Charachter after the 3 just went missing, old Data was Ok, but adding after 2016 was a mess. Prof. Dr. was just added as Pro to the Table

    I changend the Database to Access2000 and copied the Tables to the new Database
    and the problem was gone.

    I didn't have problems with Dates but that can still happen

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Re: May Day

    The only thing I can think of (and I'm definitely clutching at straws) is that "10" as a string is longer than "9" as a string. Is it possibly that you're putting the date into a string somewhere that isn't quite long enough to hold it and losing a character off the end? Try it with 5/11/2018 and see what happens.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,122

    Re: May Day

    Hi,

    are you using a Acc97 Database ?

    I picked out an old Program added an new customer with the Date above= no problem
    edited a customer with the Date above = no problem

    my String for Prefix(Mr;Mrs;Prof. Dr. etc..) was a 15 character lenght, and as I said
    it was cut of after 3 Chars.

    I also had a problem with a Acc97 Database that was corrupt- you couldn't evan delete
    the damm thing

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,238

    Re: May Day

    The program was written back in 98, so it certainly was using Access97 at the time. Sure seems like I would have moved to at least 2000, but I don't know. All I can say at this time is that it is an mdb file, so not ACCDB, and there's no changing it.

    @FD: I'm pretty sure that can't be the issue. The date IS held as a string at one point, in one table. In fact, it's part of a concatenated string for reasons I'm a bit embarrassed by, but it can't be a truncation issue, because the program has been used for a very long time, so every combination of MM/DD/YYYY, M/D/YYYY, MM/D/YYYY, and M/DD/YYYY has shown up at some point in the past. This one is no different other than the value of the date.
    My usual boring signature: Nothing

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,321

    Re: May Day

    Because May 10th 2018 was/is a Thursday?
    Somebody fiddled with the System-Properties for Date/Time?
    *shrug*
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,678

    Re: May Day

    If we're guessing, then I'd say a corruption issue. Have you done a Compact and Repair? Have you tried completely removing and re-entering the records in question or adjusting other records to these dates and see if it works. These ideas are free but just remember, You get what you pay for!!!

  8. #8

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,238

    Re: May Day

    I've tried the duplicate record. I have not tried changing a different record to those dates to see if it is picked up....well, maybe I did by accident, I have some vague memory of changing the wrong record, I just don't know whether or not I tested it.
    My usual boring signature: Nothing

  9. #9
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,278

    Re: May Day

    Possibly confusing the dates between american and english formats?
    I remember it was a bit finicky as it defaulted to american and changed to english when it couldnt analyse the date properly. If you entered it direct and the connection changed perhaps its confusing the locale on the desktop and auto converting the date.

    Is it only that one particular date?

    I always changed to dd/mmm/yyyy format to avoid problems with the dates.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  10. #10

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,238

    Re: May Day

    Shouldn't be an issue, in this case, since it's a US program that has run solely in the US for almost 20 years without any date issue. It also does appear to be just that one date.
    My usual boring signature: Nothing

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width