Runtime errors after office 365 version update

[Gasman]

Care to share, as others are reporting same error, yet they reckon everything is numeric with no quotes?

 

[shanegroff]

holder = Me![Dam]

This is the line that generates the error.

I currently have two builds running side by side on different machines.

On 2304 - 16327.20248, this does not happen.

On 2306 - 16529.20064 this does happen.

Nothing else has changed.

Can you share a simple reproducible case of this? (I mean attach a database that behaves differently in V2304 vs V2305).

It has never been allowed to assign Null to a strong variable in VBA.

Dim x as String
x = Null

will fail in any build, v2304, v2305, v2306 or any older build, this is the behavior that has always existed, so I'm not sure I understand what you are reporting.

Shane

 

[shanegroff]

I am working with Build 16.0.16227.20202 (64 bit) and now some of my SELECT DISTINCT queries are thowing a "data mis-match" error. Took me a while to figure it out but I was able to find a work-around.

Gotta LOVE MicroSoft.

By saying you were 'able to find a work-around', do you mean you think there is a bug? Or was there really a 'data mis-match'?

Can you provide an example of what wasn't working that you thought should have worked? Or are you just saying that the error message wasn't helpful?

Shane

 

[NauticalGent]

By saying you were 'able to find a work-around', do you mean you think there is a bug? Or was there really a 'data mis-match'?

Can you provide an example of what wasn't working that you thought should have worked? Or are you just saying that the error message wasn't helpful?

Shane

Absolutely, thanks for the offer. I'm away from my desk at the moment but I will give some details when I get back to it.

Thanks again!

 

[NauticalGent]

By saying you were 'able to find a work-around', do you mean you think there is a bug? Or was there really a 'data mis-match'?

Can you provide an example of what wasn't working that you thought should have worked? Or are you just saying that the error message wasn't helpful?

Shane

Here is the original query:

SELECT DISTINCT DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1) AS WorkDates
FROM t_CLIN_Data
WHERE (((DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1))>=DLookUp("[DueDate]","[t_OTD_Date]")))
ORDER BY DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1);

This worked until our IT installed the update. After the update I would get a "Data Type Mismatch In Criteria Expression" error. After playing around with it, the query would work if I removed "DISTINCT" from the SQL statement

The work-around:

SELECT DISTINCT FirstDayOfMonth([CLIN Due Date]) AS WorkDates
FROM t_CLIN_Data
WHERE (((t_CLIN_Data.[CLIN Due Date])>=DLookUp("[DueDate]","[t_OTD_Date]")));

Public Function FirstDayOfMonth(dte As Date) As Date
    FirstDayOfMonth = DateSerial(Year(dte), Month(dte), 1)
End Function

Which is pretty much the same thing but with a UDF. Below is a list of fields data types for the t_CLIN_Data table:

Attibutes of fields in t_CLIN_Data table:
CLIN_ID = 4
ContractID = 4
CLIN = 10
CLIN Due Date = 8
DLVY SCHED QTY = 7
SCHED QTY SHIPPED = 7
NSN = 10
NOUN = 10
StatusCode = 2
StatusCodeUpdate = 2
Notes = 12

Hope this answers your question, if not please ask.

 

[shanegroff]

Here is the original query:

SELECT DISTINCT DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1) AS WorkDates
FROM t_CLIN_Data
WHERE (((DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1))>=DLookUp("[DueDate]","[t_OTD_Date]")))
ORDER BY DateSerial(Year([CLIN Due Date]),Month([CLIN Due Date]),1);

Hope this answers your question, if not please ask.

I can't reproduce the error using a table with the specified structure, and the query (I also created a t_OTD_Date table with a DueDate Column of type Date/Time to make the query work). Is there any chance you could provide me with a database that shows the problem? Perhaps the problem only occurs with a specific set of data, or I'm not recreating the scenario properly. I understand if you can't share actual data, but if something broke between one version and the next, we'd certainly like to address that.

 

[tommyboy]

Can you share a simple reproducible case of this? (I mean attach a database that behaves differently in V2304 vs V2305).

It has never been allowed to assign Null to a strong variable in VBA.

Dim x as String
x = Null

will fail in any build, v2304, v2305, v2306 or any older build, this is the behavior that has always existed, so I'm not sure I understand what you are reporting.

Shane

If you PM me, then I could send you the database and instructions.

 

[NauticalGent]

I can't reproduce the error using a table with the specified structure, and the query (I also created a t_OTD_Date table with a DueDate Column of type Date/Time to make the query work). Is there any chance you could provide me with a database that shows the problem? Perhaps the problem only occurs with a specific set of data, or I'm not recreating the scenario properly. I understand if you can't share actual data, but if something broke between one version and the next, we'd certainly like to address that.

Here you go...

 

[isladogs]

@NauticalGent
The DB contain links. To test, need the actual tables

 

[NauticalGent]

@NauticalGent
The DB contain links. To test, need the actual tables

lol. Shit...moment.

 

[NauticalGent]

Try this one...

 

[isladogs]

I tested in A365 v2306 build 16529.20064 (CC Preview)
I don't think this is an Access version issue
Instead, the problem is once again due to NULL values.

You have 8 records with no date value so DateSerial (variant) shows #Error for those records
As a result, the query fails on both the sort and the filter criteria
I got the same date mismatch error with/without the DISTINCT

I believe your query using the Date function works as the #Error records are excluded due to the WHERE filter
Try removing the filter and it fails again with the data mismatch error!
Interestingly, if I then restore the WHERE filter, it still fails

Exclude or delete the null records (or add dates to those records) and both the original query & the function version work

 

[NauticalGent]

And there you have it...the last batch we imported had some null values in the date fields. After removing them, everything worked fine. I will have to check for that during future imports.

Great job Colin - now I have to take back what I said about MS...

 

[The_Doc_Man]

now I have to take back what I said about MS..

No you don't. I'm SURE they deserve the occasional heaping on of scorn, derision, and contumely.

 

[tommyboy]

I am really confused now.

If I bypass the start up options on my application (holding down shift when opening), then the form/code in question generates the runtime error in all versions of access I have loaded on my machines.

But if I open the application as it is designed to be opened (only front ends accessible), then the exact same form/code with the exact same data does not generate the errors anymore in all versions of access up to the last two (and I have used this program since 2014 with various versions of access).

Very odd.

Any ideas anyone?

Thanks

edit* It's the exact same with the 3024 error. It only gets flagged when opening the application and bypassing the startup options on older versions of access, but with the last two updates, it always gets flagged.

 

[isladogs]

@tommyboy
I don't see how anyone can advise with your latest findings as we don't know what happens in your startup code
I can only suggest that something may have changed in one or more of the various settings in Access Options in v2304 to cause this behaviour

I recommend you go through each setting systematically in v2303 & 2304 to determine what the difference(s) are

 

[tommyboy]

@tommyboy
I don't see how anyone can advise with your latest findings as we don't know what happens in your startup code
I can only suggest that something may have changed in one or more of the various settings in Access Options in v2304 to cause this behaviour

I recommend you go through each setting systematically in v2303 & 2304 to determine what the difference(s) are

Do you know where I might find the start up code please?

There has to be something that is only triggered on opening the application in the 'uniform' way and has always caused these errors to be ignored up until now, but I do not know where to find it.

I know how to investigate these things to a certain extent, but need instructions if possible!!


Thanks

 

[isladogs]

Suggest you look in

• Access Options (compare settings in the latest version with those in v2303 or earlier)
• Code in the Form Open/Load/Activate/Current/Timer events of your startup form (if you have one)
• Code in an autoexec macro (e.g. anything setting startup properties)

 

[tommyboy]

Suggest you look in

• Access Options (compare settings in the latest version with those in v2303 or earlier)
• Code in the Form Open/Load/Activate/Current events of your startup form (if you have one)
• Code in an autoexec macro (e.g. anything setting startup properties)

Many thanks.

I will investigate.

 

[tommyboy]

Suggest you look in

• Access Options (compare settings in the latest version with those in v2303 or earlier)
• Code in the Form Open/Load/Activate/Current/Timer events of your startup form (if you have one)
• Code in an autoexec macro (e.g. anything setting startup properties)

Hi

This is the code I have found runs only double clicking to open the application, rather than bypassing startup.


Function InitializeOutlook() As Boolean
' This function is used to initialize the Application and
' NameSpace variables.

On Error GoTo HandleErr
Set molApp = CreateObject("Outlook.Application")
Set molNameSpace = molApp.GetNamespace("MAPI")
InitializeOutlook = True

ExitHere:
Exit Function
HandleErr:
InitializeOutlook = False
Resume ExitHere

End Function


Does this tell you anymore at all?

Thanks