Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Date Problem 2007-07-27 14:59:39

    The above displays imported date data value in text form. (2007-07-27 14:59:39)

    There are 246,764 records in the table.
    There are 58 columns
    There are 7 separate date fields formatted in text which allows me to import them in the first place.

    I need to change the values from text to a shortDate format (7/27/2007). There is no date separator in the imported text file.

    First I tried the search and replace on the 14:59:39 portion in the field.
    The error was "You can't replace the current value of the field with the replacement text." The replacement is nothing, null or whatever you call it.

    Next I tried setting the date field length to 10.

    Access says there is not enough disk space to complete the task. I have a 200 GIG hard drive with abour 45 gigs free space.

    Next I tried this query in hopes of using an update to remove the offending 14:59:39 in each record.

    SELECT Custom_Download.PENDINGDATE
    FROM Custom_Download
    WHERE (((Custom_Download.PENDINGDATE) Like "????-??-??"));

    These three attempts will only process near 9,335 records at a time then stop.

    I tried keying and unkeying the table - no change.

    Any suggestions?

    Thanks . . Rick

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try format(cdate(left(Custom_Download.PENDINGDATE,10)) ,"mm/dd/yyyy")

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Hi Rouge - thanks for your suggestions.

    I get an "Invalid use of Null" error when I try and execute that in the query.

    any ideas . . . Rick

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by rogue
    Try format(cdate(left(Custom_Download.PENDINGDATE,10)) ,"mm/dd/yyyy")
    with an update query.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is the field empty in some records?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Rick Schreiber
    Hi Rouge - thanks for your suggestions.

    I get an "Invalid use of Null" error when I try and execute that in the query.

    any ideas . . . Rick
    ...well in which case try altering your where clause to filter out null records
    ...you may need toinvestigate further but where <mydatecolumn> <> null
    I'd rather be riding on the Tiger 800 or the Norton

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

    "Invalid use of Null"

    YES - there will be many records that have no values in them.

    This is what I have. I'm not sure how to write the null feature.

    I still get the "Invalid use of Null" error.

    SELECT Custom_Download.OFFMARKETDATE
    FROM Custom_Download
    WHERE (((Custom_Download.OFFMARKETDATE)=Format(CDate(Lef t([Custom_Download].[OFFMARKETDATE],10)),"mm/dd/yyyy"))) OR (((Custom_Download.OFFMARKETDATE)="WHERE [OFFMARKETDATE] <> NULL"));

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I've had that problem in the past. Try importing it as text. Once you import it, you can try to monkey with it in Access, either in VBA or a query.

    Sam

  9. #9
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try pasting this in the SQL view of a query.

    SELECT iif(isnull([Custom_Download]![OFFMARKETDATE]) = false,Format(CDate(Left([Custom_Download]![OFFMARKETDATE],10)),"mm/dd/yyyy"),null) as NewDate
    FROM Custom_Download;

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

    Close . . .

    Rouge - on it's own it worked flawlessy but the minute I change the query to an UpDate Query it results in the error I've attached here.

    Rick

    Wait a minute. there is a new field name [NewDate] but this date is not in the table upon execution?

    What I'm trying to do is iliminate the time portion of the OFFMARKETDATE field.

    A delete query won't work because that will delete all of the dates in the field.

    An Update query seem to be the right choice but I'm not even sure about that.

    Rick
    Attached Thumbnails Attached Thumbnails AccessError.jpg  
    Last edited by Rick Schreiber; 08-28-07 at 18:11.

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Quote Originally Posted by Sam Landy
    I've had that problem in the past. Try importing it as text. Once you import it, you can try to monkey with it in Access, either in VBA or a query.

    Sam
    Sam - that's what I did. I'm forced to immport all of the date fields as text on the import specifications.

    But from that point there is no redefining the field or setting the field length to 10 or any of the other normal work arounds.

    Rick

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    try this

    sometimes the easiest solution is the easiest.

    first create a recordset where the date = not isnull then run the query for the date values using the format function provided

    you could also use the NZ function with an alias then set nulls to "No Date" and query where the date <> "No Date"

    SELECT nz([mydate],"No date") AS thedate
    FROM Table1
    WHERE (((nz([mydate],"No date"))<>"No Date"));
    Dale Houston, TX

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

    UpDate query

    WHY DOES THIS HAVE TO BE SO COMPLICATED???

    From this "2000-03-15 00:00:00"

    To this "2000-03-15" in an update query.

    All of the suggestions are close but none complete the task.

    Why is Access so difficult? Really?

    Rick

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...it isn't
    Access, like Excel, is pretty simple, som may argue its too simple for its own good, it encourages people to dive in and 'do' somethng, something which may seem daunting in other environemnts like VB, VC, or whatever.

    And thats great.. it works well for the majority who want to stick to soemthing simple. However Access is also a farily effective front end to server based databases... to get the best out of that you need to have not only some knowledge of systems, db & application design but you also need to specialise.

    If yyou are havign to delve into the mechanics behind access to do things (eg inser code behind events, read from files, inport from other systems) then you are moving out from the "easy" bit of Access into a more developmenr / programmer arena. Its a bit like selling a house, you get what yuou pay for. In the UK the agent charges 1..2% of the purchase price to the seller, and does very very little for that, but the sale usually goes though OK, whereas.........
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your date-as-text is in ISO-8601 format - which A can understand.
    try:
    UPDATE yourtable
    SET olddate = replace(left$(olddate, 10), "-", "/")
    WHERE isdate(olddate)

    you could also convert to datetimes in a new field with cdate(olddate). the original time componant will remain but will not be displayed/reported when using regionalsettings shortdate format.

    izy
    currently using SS 2008R2

Posting Permissions

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