Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Problem with date convertion from string to date format, URGENT HELP NEEDED !!THANKS

    I make an import of a huge file txt table with headers at each page and date format as "20.12.2004"

    in order to use the data I first qo a query that exclude all line representing the headers repeated for each page.

    now that I have a txt table with several colums I convert back the comlumns in to the format I need to perform other table:

    I mainly have to convert text fields in to Dates and numbers.

    I have a problem with dates:

    considering that in the query i always start from a field of a table that has been imported as a text I have converted the date in the same way multile times but as soon as I create a new query that select a group of dates that I previously converted access tells me that there is eather a "type mismatch" or "invaid use of NULL"

    in order to convert the date in string in to a date format I have tried several formula.

    they seams all to work since I obtain a date as in example below in each of the 3cases: all theses case they convert the date from : 20.01.2004 in to 20/01/2004.

    case1)
    Data_DocNew2: DateValue(Replace([Data_Doc],".","/"))

    case2)
    Data_DocNew1: CDate(Replace([Data_Doc],".","/"))

    case3)
    Data_DocNew: DateSerial(CInt(Right(Trim([Data_Doc]),4)),CInt(Mid(Trim([Data_Doc]),4,2)),CInt(Left(Trim([Data_Doc]),2)))

    as soon as I create new query based on the new date with a simple criteria as filter: >=#01/10/2003# I get in case 1) and 2) "data mismatch", in case 3) "Invalid use of Null".

    can you please help?


    ave a look to the attached example


    Giulio
    Attached Files Attached Files
    Last edited by redyul; 07-03-04 at 13:53.

  2. #2
    Join Date
    Apr 2004
    Location
    Reading, UK
    Posts
    16
    Import the text file into a new table with the correct Access data types (not all text). All of the dates will be implicitly converted automatically without the need for any data transformation afterwards.

    If you are using a generic text data type for all fields so that all rows are always imported then use the IsDate function in your query to highlight non-dates and then copy all validated rows to the new table with the correct Access data types

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have to bring an Excel file every week and it has date that will not work

    It is in the following format....18062004 and 7062004 etc and it is something weird.

    I use the Len function in fields to get whether it is 7 or 8 characters and then the Mid function and the join them with / in the join. Sometime ago someone posted up how do it with one query field but I lost it

    I need the Len field, then 3 Mid fields to strrip off days, mths and year and then the join.

    Mike

Posting Permissions

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