?
Solved

TypeMismatch when Converting number yyyymmdd to mm/dd/yyyy

Posted on 2012-08-28
3
Medium Priority
?
856 Views
Last Modified: 2012-08-28
I need to convert a number/date field from yyyymmdd to mm/dd/yy and I am getting a data type mismatch.

HIRE_DATE: DateSerial(Left$([EFFECT_HIRE_DATE],4),Mid$([EFFECT_HIRE_DATE],5,2),Right$([EFFECT_HIRE_DATE],2))

what am I missing
0
Comment
Question by:Karen Schaefer
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38343344
Try explicitly converting to a numeric datatype:

HIRE_DATE: DateSerial(CInt(Left([EFFECT_HIRE_DATE],4)),CInt(Mid([EFFECT_HIRE_DATE],5,2)),CInt(Right([EFFECT_HIRE_DATE],2)))

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 38343359
What needs to be modified to be able to handle null value -
0
 

Author Closing Comment

by:Karen Schaefer
ID: 38343377
thanks that did it.

K
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question