Input mask to change data

[TylerTand]

I have excel spreasheet that have dates on them. But the dates are formatted as general so they are really only numbers to Access when I link the spreadsheet to a table. I was hoping that I could create an input mask that would make Access recognize that the numer 20070912 is really September 12, 2007. I can't change the structure of the spreadsheet because the data comes from a data service. But maybe I can translate this number into a date in Access? Can you geniuses help me create either an input mask or some process besides using:

transdate:
DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))

to change the number into a date? I was hoping that I could just create an input mask yyyymmdd and then Access would recognize it as a date, but it seems more complicated than this. I need to use the date() function for further analysis so it has to recognize the number as an actual date. Thanks for your help.

 

[Pat Hartman]

You need to convert the number to a date when you import the spreadsheet. You can use the expression you mentioned. Although Access stores dates as numbers internally, they are not strings as is the date you are working with.