UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> "data Type Mismatch In Criteria Expression" Only When Aggregating, Access 2013    
 
   
davidinsc
post Oct 15 2019, 10:15 AM
Post#1



Posts: 49
Joined: 29-December 14



Hello all:

I using the following SQL statement to insert records into a new table "tblA06":

SQL
SELECT
tblEligible20190930_0.MANAGER_ID AS EMP_ID,
Right([MANAGER_NAME],Len([MANAGER_NAME])-InStr(1,[MANAGER_NAME]," ")) AS LAST_NAME,
Trim(Left([MANAGER_NAME],InStr([MANAGER_NAME]," "))) AS FIRST_NAME,
"Administration" AS LOCATION_ID
INTO tblA06 FROM
tblEligible20190930_0 ;


As there are multiple records for each MANAGER_ID in tblEligible20190930_0 I would like to limit the insertion to just distinct records.

When I include a "GROUP BY" clause and attempt to run the statement I get a "Data type mismatch in criteria expression":

SQL
SELECT tblEligible20190930_0.MANAGER_ID AS EMP_ID, Right([MANAGER_NAME],Len([MANAGER_NAME])-InStr(1,[MANAGER_NAME]," ")) AS LAST_NAME, Trim(Left([MANAGER_NAME],InStr([MANAGER_NAME]," "))) AS FIRST_NAME, "Administration" AS LOCATION_ID INTO tblA06 FROM tblEligible20190930_0
GROUP BY tblEligible20190930_0.MANAGER_ID, Right([MANAGER_NAME],Len([MANAGER_NAME])-InStr(1,[MANAGER_NAME]," ")), Trim(Left([MANAGER_NAME],InStr([MANAGER_NAME]," "))), "Administration";


The data type for all fields in tblEligible20190930_0 is "Short Text".

Any idea what I'm doing wrong here?

Thanks in advance!
Go to the top of the page
 
davidinsc
post Oct 15 2019, 10:19 AM
Post#2



Posts: 49
Joined: 29-December 14



Oops! Posted this too quick. Let me work through a couple of other issues.

I'll post an update.

Please excuse the interruption! hat_tip.gif
Go to the top of the page
 
theDBguy
post Oct 15 2019, 10:19 AM
Post#3


Access Wiki and Forums Moderator
Posts: 76,566
Joined: 19-June 07
From: SunnySandyEggo


Hi. Double check to make sure there are no null values.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 03:06 AM