Turning an Excel Formula to criteria in a Query

[simon4amiee]

=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")

Is it possible to replicate the above formula from Excel into criteria in an Access Query.

Assume the cell reference $A2 is a text field which has customised date info in it but is not a recognised date format.

 

[pr2-eugin]

Give us an example of the data contained in that cell, and what this "formula" would yield for us to better understand and give a right solution !

 

[namliam]

Looks like its some kind of date format like
Jan 01 2015 probably followed by a time of some sort. Not sure what the last two characters are, perhaps AM/PM?

Mid left and right work the same in access, instead of text you would use Format, but that kindoff defeats the purpose, you want "proper" dates so why convert it back into a text?
Biggest difference being you use a field name instead of a cell name, but that should be obvious.

 

[simon4amiee]

Sample Data (received in a text file as text unfortunately)

Dec 5 2014 12:02PM
Dec 8 2014 3:36PM
Dec 9 2014 11:55AM
Dec 10 2014 12:03PM
Dec 11 2014 4:06PM
Dec 12 2014 11:41AM
Dec 15 2014 3:38PM
Dec 15 2014 10:34AM
Dec 16 2014 3:38PM
Dec 16 2014 11:03AM
Dec 17 2014 3:39PM
Dec 17 2014 12:06PM
Dec 18 2014 3:39PM
Dec 18 2014 9:29AM
Dec 19 2014 11:34AM
Dec 19 2014 12:22PM

 

[simon4amiee]

That formula works in Excel but need it in a query to be honest

 

[namliam]

Like I said, replace TEXT by FORMAT and you should be good (after changing the cell reference to a column reference obviously!)

Still remains the question, WHY, would you change it from text to date to text?

 

[namliam]

Just thinking about it, I think CDate("Dec 5 2014 12:02PM") should work as well without any special left/right/mid parts in the way.

 

[namliam]

Lets continue down the other thread, since that seems to be heading in the right direction (as well)

http://www.access-programmers.co.uk/forums/showthread.php?p=1404371#post1404371