Type Conversion Error with InStr and a date field

[PRMiller]

Queries and I are NOT getting along well this week...

Attempting to run an append query from one table to another. The selected table has a field labeled "EmpHireDate." It is a Date/Time data type with an input mask of 99/99/0000;0;_

I am attempting to split the date into month, day, and year via the query and inserting this into corresponding fields in the destination table. Here's what I've got so far (I haven't done anything with the day, yet - not sure how to approach that one):

IIf((InStr([tblEmpInfo].[EmpHireDate],"/"))>1,Left([tblEmpInfo].[EmpHireDate],2),Left([tblEmpInfo].[EmpHireDate],1)) AS [Month]

I had originally tried

Month([tblEmpInfo].[EmpHireDate]) AS [Month]

but that didn't work, so I tried the more verbose line listed above. With either one of these options I receive a "Type Conversion Error" for all records where the month portion of the string is LESS THAN 2 digits - ie 2 for February.

After playing around, I decided to change the format of EmpHireDate in tblEmpInfo to from "Short Date" to mm/dd/yyyy, thinking that perhaps the query did not like single digits as months. Still didn't work.

My year converts fine, so I'm convinced (perhaps erroneously...?) that there's nothing wrong with the EmpHireDate field.

Any thoughts as to why I'm receiving a type conversion error?

Here's the entire query:

INSERT INTO T_tblAnnivBirth ( EmpName, EmpDay, [Month], [Year] )
SELECT tblEmpInfo.EmpName, tblEmpInfo.EmpHireDate, IIf((InStr([tblEmpInfo].[EmpHireDate],"/"))>1,Left([tblEmpInfo].[EmpHireDate],2),Left([tblEmpInfo].[EmpHireDate],1)) AS [Month], Right([tblEmpInfo.EmpHireDate],4) AS [Year]
FROM tblEmpInfo
WHERE (((tblEmpInfo.EmpJobTitle)="PCA") AND ((tblEmpInfo.EmpActiveState)=-1))
ORDER BY tblEmpInfo.EmpHireDate;

 

[PHV]

Have you tried something like this ?
INSERT INTO T_tblAnnivBirth (EmpName, EmpDay, [Month], [Year])
SELECT tblEmpInfo.EmpName, tblEmpInfo.EmpHireDate, CInt(Format(tblEmpInfo.EmpHireDate,"MM")) AS [Month], CInt(Format(tblEmpInfo.EmpHireDate,"YYYY")) AS [Year]
FROM tblEmpInfo
WHERE tblEmpInfo.EmpJobTitle="PCA" AND tblEmpInfo.EmpActiveState=-1
ORDER BY tblEmpInfo.EmpHireDate;
Assuming that Month and Year are defined as numeric in T_tblAnnivBirth.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

First, I would not name a field with the name of a function. Consider HireMonth and HireYear rather than Month and Year. Also, there is generally no reason to store the month and year if you are storing the date.

Is it possible that EmpHireDate might be Null?
I would not convert to text and then back to numeric.

IIf(IsNull(EmpHireDate),Null,Month(EmpHireDate))AS [Month], IIf(IsNull(EmpHireDate),Null,Year(EmpHireDate)) AS [Year]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PRMiller]

PHV,

Once again, that did the trick. I was not previously aware of the CInt (and now CBool, CByte, CCur, etc) function for data-type conversions. I can see how that worked along with the Format..."MM" function to populate the appropriate field.

Duane, thanks for the feedback. I normally don't make it a habit to name query fields identical to functions. Thanks for catching that! While some instances of EmpHireDate were null, most were not, and the query was not erring on dates where the month was one digit instead of 2. PH's solution got me past that issue. Also, we're doing so many different things with this data that it ends up being easier to temporarily store the month and year as seperate integers.

Thankyou both for your feedback. Learning from scratch is an often-rewarding, sometimes frustrating experience. Once I have time, I think I'll stop spending so much money on Access books and actually take some classes.

Thanks,
Paul