May Day

[Shaggy Hiker]

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?

[ChrisE]

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

[FunkyDexter]

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.

[ChrisE]

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

[Shaggy Hiker]

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.

[Zvoni]

Because May 10th 2018 was/is a Thursday?
Somebody fiddled with the System-Properties for Date/Time?
*shrug*

[wes4dbt]

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

[Shaggy Hiker]

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.

[Ecniv]

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.

[Shaggy Hiker]

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.