Converting text to date

[Lucia Balkamp]

I got a crazy excel file I'm linking to a Access table. There are 4 columns with dates on the report that look like this
1100106 which is really 01/06/10 (Jan. 6, 2010)

I really need them to be in the form of 01/06/10, I can not change the excel file format it has to stay the same format for other people. So my only option is to find a way to change the format on Access.

Does anyone know how I could do this?

Thanks
Lucia

 

[boblarson]

You could use this:

DateSerial(Mid([YourFieldNameHere],2,2), Mid([YourFieldNameHere],4,2), Right([YourFieldNameHere], 2))

 

[Lucia Balkamp]

Actually, it looks like the format it is being linked to Access is a Number format.
Is there another code and does this code go in the Design View, General Tab, Format?

Thanks so much, I need a Tequila today
Lucia

 

[boblarson]

No, you can't set the format directly. You need to do this in a query to DISPLAY the field as a date.

 

[Lucia Balkamp]

Ok, I got lost but I built a query for it
but where would I place the code, I tried putting it in the Criteria and in the General tab area, the numbers did not change.

Please help me,
L

 

[boblarson]

Ok, I got lost but I built a query for it
but where would I place the code, I tried putting it in the Criteria and in the General tab area, the numbers did not change.

Please help me,
L

It goes in the FIELD area and you give it a new name:

MyDateateSerial(Mid([YourFieldNameHere],2,2), Mid([YourFieldNameHere],4,2), Right([YourFieldNameHere], 2))

 

[Lucia Balkamp]

OMG...... I would kiss you if you were here !!!!!!

but since your not I'll drink a shot for you

THANKS big time !!!!!

L