Expanding 1 Field to 2

[Guest]

I have this table where it imports some files every morning
These are the fields: FileName, Card Type, Sales, Sales $, Returns, Returns
$, Net and Net $

Now, i need to import this table to another table but the FileName field has
to be broken up to 2, Date and Location

Now the file name is something like PB010830
where 01 is location and 0830 is date. Now i got the location part working
but when i try to break that file name to a date, I get this error "Data
type mismatch in criteria expression".
I tried this:
FileName: dateValue(Mid([FileName],5,2) & "/" & Mid([FileName],7,2) & "/" &
Year(Date()))

but still error. need help

 

[Douglas J Steele]

See whether this works any better:

FileName: DateSerial(Year(Date()), Mid([FileName],5,2), Mid([FileName],7,2))

or

FileName: DateSerial(Year(Date()), CLng(Mid([FileName],5,2)),
CLng(Mid([FileName],7,2)))