title: Text to Date Conversion in Access
body: <p>I have a text column in my Access 2007 table of the format "m/d/yyyy hh:mi:ss" i.e. 1-2 digit month 1-2 digit day 4 digit year and "American" date format. The local date format is dd/mm/yyyy.</p>
  
  <p>I want to convert these dates to a Date/Time Field so I can sort on them but when I run the update query using CDate it is inconsistant in its' handling of month and day. It's OK for days > 12 because the date is unambiguous but it converts 1st of August (8/1/2011) to the 8th of January...</p>
  
  <p>I do not have the rights to change my locale - which may be a band-aid if you can do that temporarily.</p>
  
  <p>I can "force" the conversion by doing lots of work with Left, Right, Mid, InStr etc but it's a lot more work than it should be because of the 1-2 digit day and month.  </p>
  
  <p>What I would like (but can't find) is the VB equivalent of StrToDate in Borland Delphi/Pascal where you pass in the date string and a format string that tells the conversion what each digit represents.  </p>
  
  <p>In Delphi it would be as easy as:-</p>
  
  <pre><code>MyDate:= StrToDate(MyAmericanFormattedDate,'d/m/yyyy hh24:mi:ss');
  </code></pre>
  
  <p>Is there a VB equivalent?</p>