Type mismatch for date. Cannot query via direct window

[dalcazar]

Hi all,

I've been trying to get a query working that worked fine previously, but now it refuses to work properly. I'm trying to run a SQL query to append a new value to a table, but it's choking on my date submitted value and throwing a type mismatch 13 error, and I don't think that the problem lies in my SQL statement.

There is a date value saved to the date field, I can query values on other fields just fine, but if I try ?Me.Datefield in the Immediate window I get the same type mismatch error. I also tried assigning the date value to a variable and using CDate() to convert the value, but it just doesn't seem to be able to read the data.

The back end is set up on SQL server and the data type is datetime2, which I haven't had problems working with before. .

Anyone have any ideas on what the problem might be? I've not had any luck googling the problem.

 

[pbaldy]

What is the date value? There are dates Access won't mind but SQL Server will. I wasn't aware that Me would work in the Immediate window.

 

[The_Doc_Man]

Can you query the same field in other records? See what gets returned?

The problem is that things don't just break like that. EITHER (a) you changed something or (b) whoever is the DBA or Admin for the SQL server changed something or (c) Microsoft changed something.

I state it that way because if the problem occurred starting Wednesday, we DID have a Microsoft update cycle. So (c) isn't necessarily out of the question. If you were "dinking around" and the problem suddenly occurred, you may have triggered something inadvertantly and in a way that does not SEEM like it would have made a difference - but you never know until you look at it. And it doesn't hurt to ask your IT staff if THEY have changed a site policy recently.

 

[Pat Hartman]

Me. only works in the immediate window if the form is open and its code is stopped

I'm trying to run a SQL query to append a new value to a table, but it's choking on my date submitted value and throwing a type mismatch 13 error, and I don't think that the problem lies in my SQL statement.

We can't help with that since we can't see your SQL statement. Is it just ONE row? Can you identify the actual value because as Paul mentioned, Access and SQL Server use a different range of dates and so a date that is perfectly valid in Jet/ACE makes SQL Server choke.

PS, you don't Append values to a table. You append rows. You update values in existing rows.

 

[561414]

What list does intellisense provide you with if you type another dot after Datefield like Me.Datefield. ?
That should tell you what is interpreting as Me.Datefield. You can also expand your Me variable in the locals window. You will get your answers there.

 

[arnelgp]

you converted your MSSQL DateTime2 to Access Date/Time Extended, right?!
unfortunately, Access is not yet "patched" to fully recognized it:
Using the Date/Time Extended data type - Microsoft Support

i wouldn't quickly jump to new features until they are mature enough.
that include their new Webbrowser (if i know MS).

 

[dalcazar]

you converted your MSSQL DateTime2 to Access Date/Time Extended, right?!

Thanks, you found the source of the problem.

It is Date/Time Extended causing the issue, except that I didn't ask Access to make this conversion. It just did it as part of the importation steps. I don't have control over the tables housed in MSSQL and have to go thru my DB admin to get those created.

I'll post back with the final resolution to this problem once I have it working.

 

[isladogs]

There were several recent changes to how Access handles the DTE datatype
https://techcommunity.microsoft.com...n-version-2302-released-february/ba-p/3771445

Have you enabled support for DTE in Access options?

 

[arnelgp]

you can try to use post#4 in this
thread:
Query - format MS SQL date/time extened that is linked to Access as yyyy-mm-dd | Access World Forums (access-programmers.co.uk)

 

[GPGeorge]

There were several recent changes to how Access handles the DTE datatype
https://techcommunity.microsoft.com...n-version-2302-released-february/ba-p/3771445

Have you enabled support for DTE in Access options?

You also have to use an ODBC Driver that supports the extended Date/Time values.

 

[arnelgp]

on my demo, those two are not ticked.
on my pc, i checked them both and re-open the db again.
open the form for the test, still the error persists.

you can test it yourself with your own db if you don't want to download.

 

[isladogs]

There is some support for the linked DTE datatypes even without the box being ticked.
However, ticking the checkbox apparently provides additional functionality

 

[GPGeorge]

I am wondering if there is a regression. I replicated Arnel's results. Still trying to prep for the Thursday presentation and not enough time to pursue this at the moment.