Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: Union Query - Data Type Mismatch

    Hi,

    Could someone please help me.

    I'm trying to show results from 3 tables using a union all query, but when i scroll down on the results I get a Data Type Mismatch error. This is my query:

    Code:
    SELECT 
    [Outsource Company],
    [Advisor Name],
    [User ID],
    [Accreditation Date],
    NULL AS [Date of Recorded Call],
    NULL AS [Monitoring Record ID],
    NULL AS [MS Record ID]
    
    FROM [OutsourcerStaff]
    
    WHERE ((([OutsourcerStaff].[User ID]) Not Like "DP??" And 
    ([OutsourcerStaff].[User ID]) Not Like "AA??" And 
    ([OutsourcerStaff].[User ID]) Not Like "AG??" And 
    ([OutsourcerStaff].[User ID]) Not Like "ZA??") AND 
    (([OutsourcerStaff].[User Deactivated]) Like 0))
    
    UNION ALL SELECT 
    
    [OutsourceCompany] as [Outsource Company],
    [AdvisorName] as [Advisor Name],
    [UserID] as [User ID],
    NULL AS [Accreditation Date],
    [DateOfRecordedCall] as [Date of Recorded Call],
    [RecordID] as [Monitoring Record ID],
    NULL AS [MS Record ID]
    
    FROM [NewMarkingDataFTP]
    
    WHERE ((([NewMarkingDataFTP].[UserID]) Not Like "[0-9]*"))
    
    UNION ALL SELECT 
    
    [OutsourceCompany] as [Outsource Company],
    [AdvisorName] as [Advisor Name],
    [UserID] as [User ID],
    NULL AS [Accreditation Date],
    [DateOfRecordedCall] as [Date of Recorded Call],
    NULL AS [Monitoring Record ID],
    [MSRecordID] as [MS Record ID]
    
    FROM [NewMarkingDataMS]
    
    WHERE ((([NewMarkingDataMS].[UserID]) Not Like "[0-9]*"));
    The strange thing is, if i do the Union All on just 2 of the tables (any combination) it works fine, it's only when i introduce a 3rd table that I'm getting this error?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does the 3rd query work as a stand alone query?

    'data mismatch' means you are using the wrong datatype and JET is throwing a wobbler

    im suspicious of
    Code:
    NULL AS [Date of Recorded Call],
    NULL AS [Monitoring Record ID],
    NULL AS [MS Record ID]

    Code:
    ....NULL AS [Accreditation Date],
    null is a reserved word
    assumign its is OK in JET I don't understand why you are using a column called null and assignign its value to two coluimns int he recordset is beyond me
    Code:
    ...NULL AS [Accreditation Date],
    [DateOfRecordedCall] as [Date of Recorded Call],
    NULL AS [Monitoring Record ID]
    Id also strongly recommend that you use some form of naming convention for your table and column names. whether you use all lower_case_separated_byunderscore OR CamelCase matters not a lot. but avoid the use of spaces in column names.. it will work inAccess/JET but will cause problem,s elsewhere. if you need full descrpitors set he caption property in the column definition not int he column name

    as you haven't provided your table design its hard to guess where you problem, actually is. im suspcious of
    (([OutsourcerStaff].[User Deactivated]) Like 0))
    ...becuase id expect a column called OutsourcerStaff].[User Deactivated] to be a boolean true or false and therefore not matching to 0.but its perfectly possible its valid

    you shoudl be able to fold the userid into
    Code:
    where UserID not in ('DP*', 'AA*', 'AG*', 'ZA*')
    OR if you were using SQL compatability mode
    Code:
    where UserID not in ('DP%', 'AA%', 'AG%', 'ZA%')
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    24
    Thanks for trying to help.

    The User Deactivated field is saved as a Yes/No box - I've tried removing that from the query altogether but the problem still occurs, so i guess it's not the issue

    The data in the tables is saved as follows:

    Outsourcer Staff
    -Outsource Company - Short Text
    -Advisor Name - Short Text
    -User ID - Short Text
    -Accreditation Date - Date/Time (short date)
    -User Deactivated - Yes/No

    NewMarkingDataFTP (and NewMarkingDataMS)
    -OutsourceCompany - Short Text
    -AdvisorName - Short Text
    -UserID - Short Text
    -DateOfRecordedCall - Date/Time (short date)

    I've done some more testing, where i've taken out some fields and it worked. It only stops working when I introduce having 3 Dates (Accred, FTP and MS) - is there a limit or problem with using more than 2 date fields in a query like this?

    This is how it looks at the moment:
    Code:
    SELECT
    
    [OutsourceCompany] as [Company],
    [AdvisorName] as [Advisor],
    [UserID] as [ID],
    NULL AS [AccredDate],
    [DateOfRecordedCall] as [CallDateFTP],
    NULL AS [CallDateMS]
    
    FROM [NewMarkingDataFTP]
    
    UNION ALL 
    SELECT
    
    [OutsourceCompany] as [Company],
    [AdvisorName] as [Advisor],
    [UserID] as [ID],
    NULL AS [AccredDate],
    NULL AS [CallDateFTP],
    [DateOfRecordedCall] as [CallDateMS]
    
    FROM [NewMarkingDataMS]
    
    UNION ALL
    SELECT
    
    [Outsource Company] as [Company],
    [Advisor Name] as [Advisor],
    [User ID] as [ID],
    [Accreditation Date] as [AccredDate],
    NULL AS [CallDateFTP],
    NULL AS [CallDateMS]
    
    FROM [OutsourcerStaff]
    
    WHERE ([User ID] Not Like "ZA*" And [User ID] Not Like "AA*" And [User ID] Not Like "DP*" And [User ID] Not Like "AG*");

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What do you think you are doing with expression
    Null as calldatesms

    Do any if these queries provide the expected results?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    24
    I'm trying to create a report showing a list of Staff (from OutsourceStaff) and next to each persons name it would show three Date columns - AccreditationDate (OutsourceStaff), Max FTP Date (DataFTP) and Max MS Date (DataMS) (pulled from their separate tables). This is why i need them separated by using Null. They'd be grouped by the AdvisorName within the report.

    EDIT: I've had it set up as 2 tables for a while now, and it's always worked well (Accred + FTP) but I need to extend it to also include the separate MS data too. Hope that makes sense

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dont understand what you mean by se-arated by using null.
    My understanding of your sql is
    There is a column called null which you want to assign to various alias names.

    As to why you even expect to handle separators in the query mystifies me... each column in the row is a separate entity. There is no need to have a separator UNLESS you are folding several columns into one output value. Buts not what you are doing.

    Id suggest removing the null as elements
    Run each query separately
    Find where the error lies
    Then remove elements in that query to find where the problem actually is
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2012
    Posts
    24
    You've made me realise that i was going about achieving this in a really convoluted and dumb way.

    I've just managed to sort it now by using 1 table and pulling up the dates via Dlookups.

    Thanks for the advice

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Healdem, just to clarify things like "Null as calldatesms". Null is not a column here, it's a value. If I have two tables to UNION together, and one has 3 columns and the other has 2, I might do

    SELECT Field1, Field2, Field3
    FROM TableWithThree
    UNION ALL
    SELECT Field1, Field2, Null As Field3
    FROM TableWithTwo

    That allows me to get the Field3 values from the first table and simply put Nulls in that field for the second table. I've done this type of thing before with no problem. In a different situation I might do one of these:

    0 As Field3
    "" As Field3
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •