Results 1 to 8 of 8
  1. #1
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16

    Specifying an explicit data type for a generated column in a query?

    Consider the following simplified query that demonstrates my problem:

    Code:
    select t1.a, null as b, t1.c from someTable t1
    UNION ALL
    select t2.a, t2.aDateValue, t2.c from someOtherTable t2.
    What appears to be happening is that Access assigns Short Text type for the generated column. The rows from the first query contain null as expected, but the date values from the second query appear as strings of random Unicode characters.

    Click image for larger version. 

Name:	access001.png 
Views:	29 
Size:	52.1 KB 
ID:	45865

    The rows with values in orderDate come from the first query, while the ones with the (mostly) CJK strings come from the second, where the column was a Date/Time.



    I suspect Access sees a NULL value first, and decides the column is ShortText, then when it sees a date value it just interprets the binary Date/Time value as UTF-8.

    How do I tell Access that the second column is a Date/Time?

    I tried CDate(null) but Access complains "Invalid use of null"

  2. #2
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16
    After some more digging, I chanced across this article: https://support.microsoft.com/en-us/office/use-a-union-query-to-combine-multiple-queries-into-a-single-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e

    A solution is to force the query to expect the field value to be a number. That can be accomplished with the expression:

    IIf(False, 0, Null)


    The condition to check, False, will never be True, thus the expression will always return Null, but Access still evaluates both output options and decides the output to be numeric or Null.
    (my emphasis) So in my case the expression is IIf(false,cdate(0),null), which works as advertised.

    An interesting gotcha, with a hacky but useful solution.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your problem is you are not specifying the type in your union query for b - swap them round

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Agree with Ajax if query two produces dates, put it in the union first and it will pick up the type from that field.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16
    @Ajax, @Minty, That was the first thing I tried, just swapping the two queries. That threw the error about "The query is too complex" (I didn't write down the error code or complete message, unfortunately).
    Regardless, swapping the queries wouldn't be a solution because both queries contain generated (constant) null columns (this is a debit/credit consolidated ledger query from two different sources) that don't get the right type in the query result.

    The forced typing hack recommended by MS is just what I was looking for, and works reliably

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    That is a separate issue. Suggest provide your actual sql and not the mock-up

  7. #7
    jhg6308 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2021
    Posts
    16
    Quote Originally Posted by Ajax View Post
    That is a separate issue. Suggest provide your actual sql and not the mock-up
    Sorry, but the description of the problem on the MS support site matches my initial situation EXACTLY, so it is a known and documented quirk.

    The solution given makes perfect sense for forcing a literal null to have a specific type, and works as advertised.

    I tried swapping the queries after applying the MS-recommended solution, and the "query too complex" error does not occur.

    I suspect it is an extreme corner case that happens only when the engine is confused about the data type of the null columns. I don't have the time right now to go back and attempt to recreate the "query too complex" issue.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Nulls will default to a text data type and dates are numbers. As far as the query is to complex error message , that is because you are not handling nulls.

    You are not prepared to show your actual code and I’m not prepared to waste my time guessing, so good luck with your project but I’m now going to drop off this thread

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-28-2020, 10:39 PM
  2. Replies: 0
    Last Post: 09-05-2017, 10:35 AM
  3. Replies: 4
    Last Post: 06-15-2017, 09:49 PM
  4. Replies: 4
    Last Post: 10-17-2012, 07:27 AM
  5. Import to Excel from Access column of LOOKUP data type
    By Derek in forum Import/Export Data
    Replies: 1
    Last Post: 12-06-2010, 06:27 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums