Code that worked suddenly started throwing errors?

[VirtualHuck]

This is my first post, please let me know if there is a better place to post this question.

I have an access database that functions as a time log for about 10 employees (split into front and backend). It has been working well and I've slowly been making tweaks to it.

I suddenly started getting an error anytime a filter is applied on a form that is set with vb code relating to DatePart function. For example, I have an On Open Event Procedure for a form that applies a filter that uses DatePart and it has an error "Data Type mismatch in criteria expression".

The thing that has me confused is that I haven't made any changes to that form in the database and it was working for many months previously. There are other forms doing the same thing.

I'm wondering if there is any current database setting or global database setting that I could have changed mistakenly with some weird keyboard shortcut combination or something weird like that. I opened an older backup of the database and am getting the same error (it worked when it was backed up).

I took a little time trying to "fix" one of the filter expressions (no success), but then realized I have many places in the database having the same issue so it seems like the code may not be the issue, but something else...

I found one post about someone changing from ANSI-89 to ANSI-92 (or vice-versa) by mistake (setting is in Options>Object Designers>SQL Server Compatible Syntax), but that doesn't seem to be the case for me.

Can anyone think of a setting I may have changed or reference that is needed that got lost or anything? If not, I need to do more troubleshooting to provide the specific code issues, but I don't think the code is the issue.

 

[MajP]

The thing that has me confused is that I haven't made any changes to that form in the database and it was working for many months previously. There are other forms doing the same thing

People say this all the time as if vba code stops working. It rarely does unless there is some kind of patch induced bug or a broken reference. 99.9% of the time there is different data in use that previously was not tested or passed. For example it works for a while until there is a case where one argument or field value is null and that case was not accounted for.

Without seeing an example it is pretty hard to take a guess. At a minimum post the code and sample data.

 

[The_Doc_Man]

The first thing I would look for is to call up a VBA code page. From the VBA page, look at its menu bar. Follow menu path Tools >> References. One reference at a time, starting from the top, select a reference so it is highlighted. Don't check or uncheck the reference, just highlight it. Do this for each checked reference.

Below the list of references you will see information about each reference including actual file name, folder location, and some other details. If any of the references say "Broken" or "Missing" then that is your problem. You would have to uncheck the reference and then check it again. This problem happens sometimes after a Windows Update or an Office Update that gets auto-loaded.

 

[VirtualHuck]

I really appreciate both replies (@MajP & @The_Doc_Man). I was dreaming of a magic bullet of some stupid keyboard combination that I hit that changed some setting, but the point that most of these errors are in the data is very accurate. I also appreciate the specific References information on how to check that they are working. It's hard when you are slammed and something feels like it just stops working. The most efficient use of time is to go back to basics and check for updates, check the references, check the data. If that all fails then start trying to troubleshoot the more specific issues and find out what specifically is "broken" or incorrect. If I get stuck at that point, I'll reach back out. Thanks again, I needed a "big picture" reminder.

 

[MajP]

I will reiterate here because of the error
"Data Type mismatch in criteria expression"
To me this screams as bad data and really doubt a reference problem would cause this. A reference problem normally results in something "unknown or missing expression or function"

So the date has to be a real date and not a string representation. So simply test it to ensure the arguments are valid.

DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]])
The DatePart function syntax has these named arguments:
Expand table

interval	Required. String expression that is the interval of time you want to return.
date	Required. Variant (Date) value that you want to evaluate.
firstdayofweek	Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear	Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

[th]

Part​

[/th][th]

Description​

[/th]​

 

[Isaac]

there is different data in use that previously was not tested or passed

I'm just quoting this to +1 - that's almost certainly what is happening.

And - this may not make sense now, but it will eventually - you'll probably want to examine the subject of how strictly and appropriately you've typed data in terms of columns (sometimes referred to as Fields) in the Table design.

In other words, this likely happened because you have some situation similar to this:
- Column is Text in the table, but people usually enter a Number, and your code worked up until now assuming it would be Numeric and was implicitly converted from text to Number at runtime, but someone finally entered Text in the table, (which worked because the table was poorly designed and didn't enforce the front end's assumptions), and now broke the code.

Or you could choose to say the front end is at fault, for not accomodating the Table design. ("Code to a DDL extract").
The point is your FE code and your BE table design need to be strictly in synch with each other.

Don't allow data types in the tables that aren't or can't be 100% accomodated in the FE

Hopefully that gets you thinking.

 

[VirtualHuck]

And happy to report that a fresh set of eyes after taking a lunch and focusing on @MajP point that the error points to the data...

I found an error in the data, as MajP said, there was a date entry that was missing the date part of date/time. I didn't jump there initially because they all get entered programmatically. I believe I must have been looking at the data in the table and that is when I mistakenly hit my keyboard or something while the curser was in the field and I didn't notice that I changed it. Anyway, thanks for the support, it truly helped.

 

[MajP]

Updating my likely cause from 99.9% to 99.95%, for when VBA stops working.

The most common one I see is with dates is that Access and VBA will do its best to cast anything that looks like a date into a date.
So the code may not fail for "1/1/2025" to "12/1/2025" passed as strings. Although it may not be the date you expect if that is supposed to be a Non-US date. Then for some reason it throws an error on "13/1/2025" because it cannot cast that into a date since there is not13th month (even if the user meant the 13th day).

 

[Isaac]

That is so true. I'm remembering a particular migration from Access be to sql server, I tried to paste-append a bunch of "dates" from Access into a linked sql table and was surprised at all the crap that was in the Access table, datatyped properly though it was

 

[Minty]

Just doing a migration from a legacy Access app to SQL and a surprising number of 19/01/201 type dates are causing some issues.
It won't be the last time...

 

[Pat Hartman]

Just doing a migration from a legacy Access app to SQL and a surprising number of 19/01/201 type dates are causing some issues.
It won't be the last time...

That's why you always need "sanity" checks on dates. Access won't accept an "invalid" date but you end up with what you did because the typo accidentally resulted in an acceptable date. Obviously DOB and DOD cannot be in the future. But, unless you are creating a genealogy database, they probably won't be more than 80-100 years in the past either. Other logical rules apply to other types of dates.