Solved Dealing with Date/Time Extended in Query (1 Viewer)

[arnelgp]

Using the Date/Time Extended field type give many sorts of problem.
You can however create a Query and including field with Data/Time extended on it.
But if you try it in an expression, say you want to Dlookup("DateTimeExtField", "YourTable"),
or if you have created a query with DTE field in it:

DLookup("DateTimeExtField", "YourQuery")

You will get Runtime error 13: Type mismatch.

Up to now i can't get the correct "datatype" of this new field.
You can however assign it to a Variant variable.
Example, if you have a Form showing a DTE (Date/Time Extended), you can
put a command button to test it's VarType():

Dim var As Variant

Var = Me![TheDateTimeTextbox]
Debug.Print Vartype(var)

It will give you 8208.
The "normal" array is 8192 (the largest) type.
8208-9192 = 16
so there is an extra 16?

Anyway you can deal with this DTE by creating a Query from your table (say YourTable).

Query1:

SELECT YourTable.some_text_field, Day([DTEField]) AS D,
    Month([DTEField]) AS M,
    Year([DTEField]) AS Y,
    Hour([DTEField]) AS Hr,
    Minute([DTEField]) AS Mn,
    Second([DTEField]) AS Sc
FROM YourTable;

Now, from Query1 you create another query (if you like) to generate Calculated Date and Time Column:

Query2:

SELECT Query1.some_text_field, DateSerial([Y],[M],[D]) AS Dte, TimeSerial([hr],[Mn],[Sc]) AS Tim
FROM TimeElementsQ;

then when you use Dlookup() on Query2 (or query1), you won't get any errors:

Debug.Print DLookup("Dte","Query2")

If you test the Value returned from DLookup using IsDate() it will return True:

Debug.Print IsDate(DLookup("Dte","Query2"))
Returns: True

Of course there may be other ways to deal with it (i can't find any on the net except this:
Using the Date/Time Extended data type - Microsoft Support

So try experimenting on another approach of your own.