Unable to edit data in Union table

[D4WNO]

Hi everyone,

I'll try to explain the best I can as I didn't write this SQL code myself, the person who did is no longer here.

I have a Union query that is linked into about 10 different excel spreadsheets on our drive and it pulls back only 3 columns of data (Document Number, Date Registered and Date Implemented). I then have another report which refers to the results of this query and does its own thing from thereon.


Here is a sample of the SQL:



SELECT CINT(RIGHT(RT_01000.[Rev-Trac Number],5)) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;

UNION SELECT CINT(RIGHT(CC_RT_01000.[Rev-Trac Number],5)) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;

My Issue:

As it is above, the query will run, but we end up with duplicate numbers in the "Document Number". It only brings back the final 5 digits from each of these files and ignores the differenciating part of the number, the "CC_RT_" or the "RT_". This causes huge issues in the file which then goes onto use these results afterwards.

My Plan: (failed)

I thought I could simply update the "5" to an "11" to catch anything named "RT_00001" and also "CC_RT_00001" serperately so I can clearly see which is which without any duplicates. However, I recieve the below error when doing so:

"Data type mismatch in criteria expression".

Please help, I've been playing with this for hours and unfortunately many many reports reply on these results being correct. It is also not possible to change the names of the RT and CC_RT files that it links into.

[JoeM]

The CINT function converts the value to an Integer (numeric). If you are including the text portion (i.e. "RT", "CC_RT_"), those aren't valid numeric entries, which is why you are getting the Data Type mismatch error.

Try removing the CINT function from each part, i.e.

SELECT RIGHT(RT_01000.[Rev-Trac Number],11) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;

UNION SELECT RIGHT(CC_RT_01000.[Rev-Trac Number],11) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;

[D4WNO]

Thank you very much, that worked