Re: Date Conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 10/04/04 

Date: Mon, 04 Oct 2004 15:27:32 -0500

Dear Dave:

First, I recommend making a string of it MM/DD/YYYY:

MID([OriginalDate], 5, 2) & "/" & RIGHT(OriginalDate, 2) & "/" &
LEFT([OriginalDate], 4)

Take a look at this text to see it is MM/DD/YYYY. Then add a CDate()
around it:

CDATE(MID([OriginalDate], 5, 2) & "/" & RIGHT(OriginalDate, 2) & "/" &
LEFT([OriginalDate], 4))

Check that this appears to work properly. By putting the original
string next to the converted date column you can do this easily and
quickly.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 4 Oct 2004 12:48:10 -0700, "Dave Waling"
<anonymous@discussions.microsoft.com> wrote:

>I'm linking to a maninframe database and in access it
>converts dates to a text data field in the format of
>yyyymmdd. This is formatted as text and I can't change
>the original database.
>
>How can I convert the text data into a date format in
>Access that I can use in regular Queries?

Relevant Pages

  • Re: dates in query HELP PLEASE!
    ... Regardless of what your regional settings have the short date format set to, ... date to mm/dd/yyyy format if that makes sense. ... > Then I append the where string to the rest of the SQL string, in order to> run it later. ...
    (microsoft.public.access.forms)
  • datagrid sort Date column as string
    ... I got a column in datagid with 'mm/dd/yyyy' date format ... The problem is that this column is sorted as string that make: ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • datenum conversions
    ... I have some data in the format of 'mm/dd/yyyy, HH:MM:SS.FFF', and I use datenumto represent it. ... I created a vector of time differences: ... how do I convert the result buck to date string? ...
    (comp.soft-sys.matlab)
  • Re: convert a number in "12345" to 12345
    ... that will return the numeric value of the string. ... NEW query ... This is my data field - Claim_Number - ... The data is in text format and has strings. ...
    (microsoft.public.access.queries)
  • Re: Inserting date datatypes via ado.net
    ... string" with> ... The date format I am using for 'thedate' is mm/dd/yyyy. ...
    (microsoft.public.dotnet.general)