Why won't this formula work in Access?

[TimD67]

I have tried to convert a formula in excel for Access but it is not being recognised.

Excel formula is:
=DATE(MID(F1,7,4),MID(F1,4,2),LEFT(F1,2))

Converted to:
Date(MID([Bookings data]![date],7,4),MID([Bookings data]![date],4,2),LEFT([Bookings data]![date],2))

Issue is my data has what looks like a date but is not recognised as such and also contains a number of blank spaces at the end of the string, like this.

27/09/2017__________ (without the underscores obviously).

I am sure it is something to do with the placing of the brackets but I can never figure this out.

 

[Joe4]

The function is slightly different in Access:

[COLOR=#333333]Date[/COLOR][COLOR=#ff0000][B]Serial[/B][/COLOR][COLOR=#333333](MID([Bookings data]![date],7,4),MID([Bookings data]![date],4,2),LEFT([Bookings data]![date],2))[/COLOR]

 

[TimD67]

Excellent, thank you Joe4.

 

[Joe4]

You are welcome!