CDate results in Data Type Mismatch in Query & Excel Pivot

G

Guest

I have data that is in a text format for employees that includes birth date,
hire date, termination date, etc. The date format is text and is written
YYYYMMDD (i.e. 20070719). I have used the CDate to convert, but get data
type mismatch when I try to connect it to a Pivot table and in some instances
when the query runs. I've also tried DateSerial and several others, with
little success. I have removed the CDate and replaced with datevalue (see
below), but I'm still getting the data type mismatch.

Any help would be greatly appreciated. SQL of the Access query is below.

SELECT dbo_EMPLOYEE.E_EMPL_NO AS [Empl No], dbo_EMPLOYEE.E_COMPANY AS
Company, dbo_EMPLOYEE.E_LAST_NAME AS Last_Name, dbo_EMPLOYEE.E_FIRST_NAME AS
First_Name, dbo_EMPLOYEE.E_MI_NAME AS MI, dbo_EMPLOYEE.E_LOCATION AS
Location, Trim([last_name]) AS [Last Name], Trim([first_name]) AS [First
Name], Trim([mi]) AS MI_, ([last name]) & ", " & ([first name]) & " " &
([mi_]) AS Name, dbo_EMPLOYEE.E_IDENT AS Ident, dbo_EMPLOYEE.E_CLASS AS
Class_Code, dbo_EMPLOYEE.E_STATUS AS Status_Code,
dbo_EMPLOYEE.E_POSITION_CODE AS Position_Code, dbo_EMPLOYEE.E_JOB_CODE AS
Job_Code, dbo_EMPLOYEE.E_COUNTRY AS Country_Code,
dbo_EMPLOYEE_BASIC.EB_BIRTH_DATE, Left(Trim([eb_birth_date]),4) AS [DOB
year], Mid([EB_birth_DATE],5,2) AS [DOB Month], Left([eb_birth_date],2) AS
[DOB Day], [dob month] & "/" & [dob day] & "/" & [dob year] AS DOBTest,
DateValue([dobtest]) AS DOB, (Now()-[DOB])/365.25 AS Age_calc,
Format([age_calc],"Fixed") AS Age, dbo_EMPLOYEE_BASIC.EB_ADM_HIRE_DATE,
Left(Trim([eb_adm_hire_date]),4) AS [Hire year], Mid([EB_adm_hire_DATE],5,2)
AS [Hire Month], Left([eb_adm_hire_date],2) AS [Hire Day], [hire month] & "/"
& [hire day] & "/" & [hire year] AS Hiretest, DateValue([hiretest]) AS
Hire_Date, Trim([EB_PRED_HIRE_DATE]) AS EB_Pred_Hire,
Left(Trim([eb_pred_hire]),4) AS [PreCoHire year], Mid([EB_pred_hire],5,2) AS
[PreCoHire Month], Left([eb_pred_hire],2) AS [PreCoHire Day],
IIf(IsNull([eb_pred_hire]),"",[precohire month] & "/" & [precohire day] & "/"
& [precohire year]) AS PreCoHiretest,
IIf(IsNull([eb_pred_hire]),"",DateValue([precohiretest])) AS PreCo_Hire_Date,
IIf(IsNull([eb_pred_hire]),0,DateDiff("yyyy",[preco_hire_date],[hire_date]))
AS PreCoTenure, ((Now()-[hire_date])/365.25)+[precotenure] AS Tenure_Calc,
Format([tenure_calc],"Fixed") AS Tenure, IIf([eb_adm_hire_date] Between
"20060701" And "20060930","Q1 2007",IIf([eb_adm_hire_date] Between "20061001"
And "20061231","Q2 2007",IIf([eb_adm_hire_date] Between "20070101" And
"20070331","Q3 2007",IIf([eb_adm_hire_date] Between "20070401" And
"20076030","Q4 2007",IIf([eb_adm_hire_date] Between "20050701" And
"20060630","Fiscal 2006",IIf([eb_adm_hire_date] Between "20070701" And
"20070930","Q1 2008",IIf([eb_adm_hire_date] Between "20040701" And
"20050630","Fiscal 2005",IIf([eb_adm_hire_date] Between "20030701" And
"20040630","Fiscal 2004",IIf([eb_adm_hire_date] Between "20020701" And
"20030630","Fiscal 2003",IIf([eb_adm_hire_date] Between "20010701" And
"20020630","Fiscal 2002","Pre 2002")))))))))) AS [Fiscal Hire],
dbo_EMPLOYEE_BASIC.EB_TERM_DATE, (Mid([eb_term_date],5,2)) AS [Term month],
Right([eb_term_date],2) AS [Term day], Left([eb_term_date],4) AS [Term year],
[hire month] & "/" & [hire day] & "/" & [hire year] AS Termtest,
IIf(IsNull([eb_term_date])," ",DateValue([termtest])) AS [Term Date],
IIf([eb_term_date] Between "20060701" And "20060930","Q1
2007",IIf([eb_term_date] Between "20061001" And "20061231","Q2
2007",IIf([eb_term_date] Between "20070101" And "20070331","Q3
2007",IIf([eb_term_date] Between "20070401" And "20070630","Q4
2007",IIf([eb_term_date] Between "20050701" And "20060630","Fiscal
2006",IIf([eb_term_date] Between "20070701" And "20070930","Q1
2008",IIf([eb_term_date] Between "20040701" And "20050630","Fiscal
2005",IIf([eb_term_date] Between "20030701" And "20040630","Fiscal
2004",IIf([eb_term_date] Between "20020701" And "20030630","Fiscal
2003",IIf([eb_term_date] Between "20010701" And "20020630","Fiscal 2002","Pre
2002")))))))))) AS [Fiscal Term], dbo_EMPLOYEE_BASIC.EB_MARITAL_STATUS AS
[Marital Status], dbo_EMPLOYEE_BASIC.EB_SEX AS Gender,
dbo_EMPLOYEE_BASIC.EB_REPORTS_TO_NO AS [Reports to id],
dbo_EMPLOYEE_BASIC.EB_REPORTS_TO_NAME AS [Reports to Name]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMPLOYEE_BASIC ON dbo_EMPLOYEE.E_EMPL_NO =
dbo_EMPLOYEE_BASIC.EB_EMPL_NO
 
D

Douglas J. Steele

CDate doesn't recognize 20070719 as a valid date: it needs to be 2007-07-19.

Rather than CDate([MyDateField]), try CDate(Format([MyDateField],
"####-##-##"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


beckster6701 said:
I have data that is in a text format for employees that includes birth
date,
hire date, termination date, etc. The date format is text and is written
YYYYMMDD (i.e. 20070719). I have used the CDate to convert, but get data
type mismatch when I try to connect it to a Pivot table and in some
instances
when the query runs. I've also tried DateSerial and several others, with
little success. I have removed the CDate and replaced with datevalue
(see
below), but I'm still getting the data type mismatch.

Any help would be greatly appreciated. SQL of the Access query is below.

SELECT dbo_EMPLOYEE.E_EMPL_NO AS [Empl No], dbo_EMPLOYEE.E_COMPANY AS
Company, dbo_EMPLOYEE.E_LAST_NAME AS Last_Name, dbo_EMPLOYEE.E_FIRST_NAME
AS
First_Name, dbo_EMPLOYEE.E_MI_NAME AS MI, dbo_EMPLOYEE.E_LOCATION AS
Location, Trim([last_name]) AS [Last Name], Trim([first_name]) AS [First
Name], Trim([mi]) AS MI_, ([last name]) & ", " & ([first name]) & " " &
([mi_]) AS Name, dbo_EMPLOYEE.E_IDENT AS Ident, dbo_EMPLOYEE.E_CLASS AS
Class_Code, dbo_EMPLOYEE.E_STATUS AS Status_Code,
dbo_EMPLOYEE.E_POSITION_CODE AS Position_Code, dbo_EMPLOYEE.E_JOB_CODE AS
Job_Code, dbo_EMPLOYEE.E_COUNTRY AS Country_Code,
dbo_EMPLOYEE_BASIC.EB_BIRTH_DATE, Left(Trim([eb_birth_date]),4) AS [DOB
year], Mid([EB_birth_DATE],5,2) AS [DOB Month], Left([eb_birth_date],2) AS
[DOB Day], [dob month] & "/" & [dob day] & "/" & [dob year] AS DOBTest,
DateValue([dobtest]) AS DOB, (Now()-[DOB])/365.25 AS Age_calc,
Format([age_calc],"Fixed") AS Age, dbo_EMPLOYEE_BASIC.EB_ADM_HIRE_DATE,
Left(Trim([eb_adm_hire_date]),4) AS [Hire year],
Mid([EB_adm_hire_DATE],5,2)
AS [Hire Month], Left([eb_adm_hire_date],2) AS [Hire Day], [hire month] &
"/"
& [hire day] & "/" & [hire year] AS Hiretest, DateValue([hiretest]) AS
Hire_Date, Trim([EB_PRED_HIRE_DATE]) AS EB_Pred_Hire,
Left(Trim([eb_pred_hire]),4) AS [PreCoHire year], Mid([EB_pred_hire],5,2)
AS
[PreCoHire Month], Left([eb_pred_hire],2) AS [PreCoHire Day],
IIf(IsNull([eb_pred_hire]),"",[precohire month] & "/" & [precohire day] &
"/"
& [precohire year]) AS PreCoHiretest,
IIf(IsNull([eb_pred_hire]),"",DateValue([precohiretest])) AS
PreCo_Hire_Date,
IIf(IsNull([eb_pred_hire]),0,DateDiff("yyyy",[preco_hire_date],[hire_date]))
AS PreCoTenure, ((Now()-[hire_date])/365.25)+[precotenure] AS Tenure_Calc,
Format([tenure_calc],"Fixed") AS Tenure, IIf([eb_adm_hire_date] Between
"20060701" And "20060930","Q1 2007",IIf([eb_adm_hire_date] Between
"20061001"
And "20061231","Q2 2007",IIf([eb_adm_hire_date] Between "20070101" And
"20070331","Q3 2007",IIf([eb_adm_hire_date] Between "20070401" And
"20076030","Q4 2007",IIf([eb_adm_hire_date] Between "20050701" And
"20060630","Fiscal 2006",IIf([eb_adm_hire_date] Between "20070701" And
"20070930","Q1 2008",IIf([eb_adm_hire_date] Between "20040701" And
"20050630","Fiscal 2005",IIf([eb_adm_hire_date] Between "20030701" And
"20040630","Fiscal 2004",IIf([eb_adm_hire_date] Between "20020701" And
"20030630","Fiscal 2003",IIf([eb_adm_hire_date] Between "20010701" And
"20020630","Fiscal 2002","Pre 2002")))))))))) AS [Fiscal Hire],
dbo_EMPLOYEE_BASIC.EB_TERM_DATE, (Mid([eb_term_date],5,2)) AS [Term
month],
Right([eb_term_date],2) AS [Term day], Left([eb_term_date],4) AS [Term
year],
[hire month] & "/" & [hire day] & "/" & [hire year] AS Termtest,
IIf(IsNull([eb_term_date])," ",DateValue([termtest])) AS [Term Date],
IIf([eb_term_date] Between "20060701" And "20060930","Q1
2007",IIf([eb_term_date] Between "20061001" And "20061231","Q2
2007",IIf([eb_term_date] Between "20070101" And "20070331","Q3
2007",IIf([eb_term_date] Between "20070401" And "20070630","Q4
2007",IIf([eb_term_date] Between "20050701" And "20060630","Fiscal
2006",IIf([eb_term_date] Between "20070701" And "20070930","Q1
2008",IIf([eb_term_date] Between "20040701" And "20050630","Fiscal
2005",IIf([eb_term_date] Between "20030701" And "20040630","Fiscal
2004",IIf([eb_term_date] Between "20020701" And "20030630","Fiscal
2003",IIf([eb_term_date] Between "20010701" And "20020630","Fiscal
2002","Pre
2002")))))))))) AS [Fiscal Term], dbo_EMPLOYEE_BASIC.EB_MARITAL_STATUS AS
[Marital Status], dbo_EMPLOYEE_BASIC.EB_SEX AS Gender,
dbo_EMPLOYEE_BASIC.EB_REPORTS_TO_NO AS [Reports to id],
dbo_EMPLOYEE_BASIC.EB_REPORTS_TO_NAME AS [Reports to Name]
FROM dbo_EMPLOYEE INNER JOIN dbo_EMPLOYEE_BASIC ON dbo_EMPLOYEE.E_EMPL_NO
=
dbo_EMPLOYEE_BASIC.EB_EMPL_NO
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type mismatch in Function 5
Data Type mismatch in a query that uses a calculated field from another query 4
Error 3464 Data Type mismatch in criteria expression 2
data type mismatch in report criteria expression 4
Access 2000: Data Type Mismatch in query 2
'Data Type Mismatch In Criteria expression' error when make-table query runs 4

Top