Date Time Extended data type? (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:50
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 16:50
Joined
May 21, 2018
Messages
8,463
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.



IDDTEDataDTData
11/1/2 1:01:03.1234567 AM1/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

MVP / VIP
Local time
Today, 20:50
Joined
Jan 14, 2017
Messages
18,186
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

You've got your good things, and you've got mine.
Local time
Today, 16:50
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 21:50
Joined
Nov 24, 2017
Messages
57
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🤔
 

Bieke

Member
Local time
Today, 21:50
Joined
Nov 24, 2017
Messages
57
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

AWF VIP
Local time
Today, 20:50
Joined
Jul 26, 2013
Messages
10,355
@MajP Have you checked this option?
1640342660422.png

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

isladogs

MVP / VIP
Local time
Today, 20:50
Joined
Jan 14, 2017
Messages
18,186
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

You've got your good things, and you've got mine.
Local time
Today, 16:50
Joined
May 21, 2018
Messages
8,463
I am going to see if i can manipulate it as a string and then convert. But cdate fails.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:50
Joined
Feb 19, 2013
Messages
16,553
from the other thread, try

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:50
Joined
May 21, 2018
Messages
8,463
@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

Super Moderator
Staff member
Local time
Today, 20:50
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
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.
 

Users who are viewing this thread

Top Bottom