Malcolm-
Here's my earlier reply:
Malcolm-
First question comes to mind is do you have any times other than GMT? Second question: are the day and month names always exactly three characters? If the answers to both questions are YES, then you can do something like:
DBDateField = #" & Mid([DateField], 5, 6) & ", " & Right([DateField], 4) & " " & Mid([DateField], 12, 8) & "#"
The literal generated by the expression above on the sample value you provided should look like:
#Jan 09, 2015 17:20:21#
The hashtags on either end tell Access that you're defining a date/time literal.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 17, 2015, at 7:41 AM, 'Malcolm Alberry' malcolm.alberry@btinternet.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi
A little help please – my first posting – I am at the start of my learning curve with MS Access 2010
I am trying to sort a CSV file on a date column in a new file that is created daily from another software process and then merge this new file with previous daily records.
As far as I can establish, the date field is a text output, and I cannot find any way how to convert the format "Fri Jan 09 17:20:21 GMT 2015" into a sortable Access date format, including time as well.
I have successfully used a numeric yymmddhhss format in the past for sorting on records manually, created from the above format (*e.g. 150109172021) using several "find and replace" changes using Excel and then importing this file to Access, but it is a bit laborious and prone to my manipulation errors!!
I am sure that there is a recognised Date format that would produce the same sorting ability that I can possibly create once the file is within Access using Access capabilities
I am happy to be able to create a new "sorted date column"!!
I would want to do this conversion daily to new output as a single step if possible, before a new merger if necessary or whenever you would recommend
Thank you
Malcolm in Linslade UK
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar