Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Change Text to Date/Time field in a query

    I have created a table called - "Test"
    The properties of the table is listed below

    Table Name: Test
    Field Name: ADMDAT2 (Text)
    DISDAT2 (Text)
    Operation Date (Date/Time)

    I have written a query to populate a field where the Operation Date is between the ADMDAT2 and DISDAT2

    Expr1: IIf([ADMDAT2] Is Null,"",IIf([Operation Date]>=[ADMDAT2] And [Operation Date]<=[DISDAT2],"Match"))

    Unfornately it returns and ERROR message... I believe this may be because, the data type of the field, matching a Text with a Date/Time, I have rerun the query using a sample table where all the fields are Date/Time, and it work perfectly.

    What i need help with how do i convert a text field into a Date/Time in a query?

    So i can place that in the query before i populate the Test table. therefore it all should be date/time

    thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You are correcting your table design right?

    Have you tried the CDATE() function?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - what date format have you used in the text column?

  4. #4
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by pootle flump
    BTW - what date format have you used in the text column?


    Code:
    ADMDAT2	         DISDAT2	        Operation Date
    20/02/2007	16/03/2007	21/02/2007
    20/02/2007	16/03/2007	05/03/2007
    20/02/2007	16/03/2007	22/02/2007
    17/10/2007	19/10/2007	05/11/2007
    29/10/2007	02/11/2007	05/11/2007
    04/11/2007	23/11/2007	05/11/2007

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - DD/MM/YYYY. That's a problem. CDATE will treat this as MM/DD/YYYY if it can, and DD/MM/YYYY otherwise. It is an annoying American centricity.

    First of all let's check there is no guff.


    Code:
    SELECT *
    FROM myTable
    WHERE IsDate(ADMDAT2) = 0
    OR IsDate(DISDAT2) = 0
    If that returns any rows then you have some dodgy data in the text columns.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    You are correcting your table design right?
    I hope the OP is correcting their table design!
    They gave us a datetime datatype for a reason, you know?
    George
    Home | Blog

Posting Permissions

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