Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: TEXT DATE Conversion

    I have date fields in a text format as mm/dd/yyyy, thanks to some suggestions in this forum.

    Access 2003 still does not allow me to redefine from text to DATE while in table design mode.

    This query "right aligns" the mm/dd/yyyy but I still cannot redefine TEXT to DATE.
    Code:
    SELECT Format(CDate([list date]),"mm/dd/yyyy") AS ExprLD
    , Format(CDate([pending date]),"mm/dd/yyyy") AS ExprPEND
    , Format(CDate([Off Market date]),"mm/dd/yyyy") AS ExprOMD
    , Format(CDate([status change date]),"mm/dd/yyyy") AS ExprSTATUS
    , Format(CDate([modified date]),"mm/dd/yyyy") AS ExprMD
    , Format(CDate([dte]),"mm/dd/yyyy") AS ExprCOE
    FROM tblsandicordownload;
    Any ideas . . ?

    Thanks . . . Rick

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A SELECT query does not modify the original data in a table (here tblsandicordownload), you need an UPDATE query for that.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What exactly are you trying to do, here? I ask because Access is actually quite lenient when it comes to interpreting what is a 'date.'

    If, for instance, you have a Field named PseudoDate, defined as Text, and you use it as a Date, doing something like this:

    Add7Days = DateAdd("d", 7, Me.PseudoDate)

    With Values, in PseudoDate (a Text Value) of May 8, 2012, 5/8/2012 or 5/8/12, Access will return Add7Days as 5/15/2012, even though PseudoDate is defined with a Datatype of Text in the underlying Table.

    The only time you'll have a problem with this is when entering Dates in United Kingdom-like format of dd/mm/yy. And this, of course, presents problems even when a Field is defined as Date/Time!

    So perhaps you don't really need to convert it back to a 'Date.'

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Also that out the spaces in the feild name
    That a BAD BAD habbit to get into
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As StePhan says, make your life easy and avoid using names like

    status change date


    with Spaces! The practice can lead to all kinds of problems! Much better to use

    StatusChangeDate

    or

    status_change_date

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    eek what the ....
    Format(CDate([list date]),"mm/dd/yyyy")
    you are converting a volumn in the db to a date value then formatting it
    wrong approach

    convert the text value to a date and store it in the db as a date column
    cdate converts a valid string expression to a date variable.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Open Space field data

    Like . . . status_change_date

    You know I'm aware that fields should at least have bracketing [status_change_date] around them.

    Better yet - I can remove the spaces on my import specs.

    In the mean time how about this. . .
    Code:
    UPDATE tblsandicorDownload SET tblsandicorDownload.[Pending Date] = NOT NULL (Mid([pending date],6,2) & "/" & Mid([pending date],9,2) & "/" & Left([pending date]),4);
    I can run it but there is no change in the data since I placed the NOT NULL in there.

    If I execute it using the red ! I get an invalid use of . , ! 0

    With hundreds of thousands of records I get NULL fields filled with //

    Any suggestions?

    Thanks as always . . . Rick

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    what do you think
    ...SET tblsandicorDownload.[Pending Date] = NOT NULL
    means or is meant to do.


    ive seen NOT NULL in a where clause but its a new one on me where you are using it
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Still NOT Cutting it after three days . . .

    Because the data in the date field REMAINS TEXT as mm/dd/yyyy.

    If I query mm/dd/yyyy even with leading 0's I get a "data mismatch in criteria expression" error.

    I used Between >="03/01/2012" And <="03/31/2012."

    I cannot query using COUNT. Same error if I use #mm/dd/yyyy#

    What is the problem here?

    Is there no clear cut answer without calling in NASA?


  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you used
    Code:
    Between >="03/01/2012" And <="03/31/2012."
    then your problem lies in the trailing fullstop
    try
    Code:
    Between >="03/01/2012" And <="03/31/2012."
    instead

    be very very wary using between on text values

    my guess is this isn't working because you insist on storing dates as text not date times.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    hearldem said . . .

    "my guess is this isn't working because you insist on storing dates as text not date times."

    THIS HAS BEEN MY QUESTION ALL ALONG! ! !

    I don't insist at all!

    My import source is terribly lousy, no date formats, currency formats, nothing. ALL TEXT.

    I cannot change the field from a TEXT to a DATE field - even if I have a text string of mm/dd/yyyy in the date field.

    I use many date range queries with COUNT.

    It would make things REALLY easy if there was a SIMPLE way to convert the TEXT string date into a DATE formatted field.

    Where to now?

    Rick

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the normal process as you import data is to convert to the correct datatype. thast is what you should be doing. if you have data coming from an external source, whether thats a commercial data feed or users you cannot trust it. validate the data as it comes into your system.

    how you do that validation is up to you.
    some advocate reading the file and checking the data is correct before entering it, to allow the user to correct errors and then reprocess
    others read the file accept what they can leaving the remainder in the file (effectivley rewrite the file) to allow the user to correct errors and then reprocess
    others import the file into specific holding temporary tables then transfer what is valid into the system leaving stuff needing re working in the holding tank.
    personally I prefer the latter approach as you can accpet the data in 'their' format, then validate the data, convert where required, then insert into your main tables, removing any processed rows.

    that is where you use
    cdate (to convert a valid date string into a datetime variable)
    ccur (to convert to a valid currency column and so on.
    look up the ms access conversion functions

    if your data supplier cannot supply you with valid data then you need to check that incoming data and supply valid values such as NULL

    yet you do insist on storing datrtiem data as string. yet you then go through the bizzaire process of convert a value to a datetime variable then formatting the datetiem variable into a string.. thats what format does.

    bear in mind there are at leasst two level s in a db application. there is where you store the data (the tables) and where you present the data (ie where the formatting goes on).

    It would make things REALLY easy if there was a SIMPLE way to convert the TEXT string date into a DATE formatted field.
    yes there is its called CDATE but it only works with valid date formats
    if you need to check if its a validf format then test it first using the IsDate function

    however whether those functions are easy enough for you to use I don't know as it means writing VBA code.

    if you are not able willing or prepared to dive into using VBA then I doubt you will find anything 'easy' about what you are trying to do. there is a limit to what sites like this can do. its visited by people able and willing to help but they cannot teach.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I can do some VBA stuff.

    Code:
    MsgBox "Normalize City Codes"
    Dim strSQL As String
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [City-Area-List] ON [tblCARETSData].City = [City-Area-List].CityName Set [tblCARETSData].City = [City-Area-List].[CityID]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CITY CODES"
    Is this VBA? I think it is.

    Is there a way in Access 2003, while IMPORTING to modify this text string 2011-05-29T00:00:00 into a date type?

    I can't see it if there is.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Have a nice day!

  15. #15
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I can do some VBA stuff.

    Code:
    MsgBox "Normalize City Codes"
    Dim strSQL As String
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [City-Area-List] ON [tblCARETSData].City = [City-Area-List].CityName Set [tblCARETSData].City = [City-Area-List].[CityID]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING CITY CODES"
    Is this VBA? I think it is. I call this with a command button.

    Is there a way in Access 2003, while IMPORTING to modify this text string 2011-05-29T00:00:00 into a date type?

    Actually yes I discovered that there is but upon declaring my date fields a Date/Time I get an error upon importing that the import failed and each of the date fields displays #num only in the top row.

    Access cannot understand this data: 2011-05-29T00:00:00

    What next . . .

    Rick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •