Date Time Extended data type?

[MajP]

Has anyone work with this data type? Is there any tricks. I helped someone with a database that was originally just a standard date. When they converted the field to date time extended it broke a lot of things.
Most date functions and formatting functions do not work with this. Things like cdate, isdate, dateserial, year, month, etc fail with a datatype mistmatch. I cannot pass the value as a date.

The strangest thing is that in a recordset the date time extended fields appears to be returned as a string. I am able to do string manipulations on them but cannot treat them like date values.

In this part of the code I return and RS with two date time extended fields. RS!dtsStart and rs!dtsStop
The date functions do not work, but string functions do such as "left". I tried to format as string first and then convert to date, and even that failed.

Set rs = CurrentDb.OpenRecordset("Select * from alarms where dateSerial(Year([dtsStart]),Month([dtsStart]),day([dtsStart])) = #" & Format(dtmDate, "MM/DD/yyyy") & "# AND [name] = '" & theName & "'", dbOpenDynaset)

a

 

[MajP]

A little research and found it is not compatible with vba and it is casted as string like I suspected.

Using the Date/Time Extended data type as a string in VBA

As of today, you cannot perform calculations on the data type in VBA code, however you can use the new data type in VBA as text (string). The following VBA example uses Data Access Object methods to display, enter, and evaluate the Date/Time Extended data type based on the table below.

ID	DTEData	DTData
1	1/1/2 1:01:03.1234567 AM	1/1/2001

Table name: DTETable
ID data type: Autonumber
DTEData data type: Date/Time Extended
DTData data type: Date/Time



If you’d like to reference the type in VBA code, please be aware that expressions on the type are not supported, given that the data will be casted as a text. However, performing calculations of the new type in VBA code is an investment that the Access team is planning for at a later date

 

[isladogs]

You ae correct that there are issues using the Date Time Extended datatype in Access.
You might find the discussion in this AUG video on the DTX datatype useful:

 

[MajP]

Thanks. They discovered about all the issues I was seeing. In Sql it seems you can use most of the date functions. In VBA none work. In VBA it appears to act as a string. So I think I need to use string functions to then parse it and create a date.

 

[Bieke]

Thanks. They discovered about all the issues I was seeing. In Sql it seems you can use most of the date functions. In VBA none work. In VBA it appears to act as a string. So I think I need to use string functions to then parse it and create a date.

I also saw the video, now i have a probleem

 

[isladogs]

I also saw the video, now i have a probleem

What is your problem?

 

[Bieke]

What is your problem?

In the database shopfloor that i posted, i have to calculate the differents in time between dtsstart and dtsstop and with this datatype and tv-held vba code in this DB it is not possible anymore because the previous datatype was date/time and they changed in extended date and time.

 

[Minty]

@MajP Have you checked this option?

It does "something" to the datbase engine to support the data type.

 

[Bieke]

@MajP Have you checked this option?
View attachment 97128
It does "something" to the datbase engine to support the data type.

Yes i did.

 

[isladogs]

Suggest you watch the video again. We discussed ways of getting rid of the DTX datatype without losing data.
See around 17:09 and again later. Look at the video times for relevant sections

 

[MajP]

I am going to see if i can manipulate it as a string and then convert. But cdate fails.

 

[CJ_London]

from the other thread, try

cdate(left("7/01/2021 20:45:57.0000000",instr("7/01/2021 20:45:57.0000000",".")-1))

 

[MajP]

@CJ_London
Thanks. I tried about 10 different things and that is the only thing that worked. In the query I took a time serial and created a time. It looked correct in the recordset. However If I passed that value to another function as a date or a double it passed as 0. These are a true PITA for use in Access.

 

[CJ_London]

Seem to recall there was a similar issue a couple of months ago with a revised data type not ‘sticking’ when query with the conversion was referenced in another query

 

[arnelgp]

the only approach to this New date, is to create 2 "normal"
date/time field to the Alarm table (or you can use Temporary table)
and use a "simple" Update Query to update these fields from
DTSSTART, DTSSTOP fields.
then you can use the 2 new field in your query/vba, etc.