Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Expression (A2k)

    The following qry pulls the date from a string:

    Completed 02/06/2006 15:00:55 TONY01
    or
    Completed 27/06/2006 12:01:49 WORKSHOP01
    Running the qry works until I try to use it in a report ? Is my method correct or is there a different expression to use ?

    SELECT tblProg.EstNo, tblProg.Supp, tblProg.Complete, tblProg.Notes, DateValue([RightDate]) AS CorrectDate, Left([notes],20) AS LeftDate, Right([Leftdate],10) AS RightDate
    FROM tblProg
    WHERE (((tblProg.Complete)=-1));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression (A2k)

    It works when I try it. What is the problem exactly?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression (A2k)

    I get the "Data Type Mismatch In Query Expression" error ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression (A2k)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression (A2k)

    Ok, its attached.
    Thanks for the help Hans....

    I see you're still very strong on the site !!
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression (A2k)

    Hi Dave,

    The problem is that there are records where the Notes field is empty or doesn't contain a valid date, but for example "Late Finish". You can solve this by changing the definition of CorrectDate to

    CorrectDate: IIf(IsDate([RightDate]),DateValue([RightDate]),Null)

    If you want to suppress the records without a valid date, you can create a new query

    SELECT qryDates.*
    FROM qryDates
    WHERE (((qryDates.CorrectDate) Is Not Null));

    and use this as record source for the report. See attached version.
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression (A2k)

    Thank You Hans

    I see where my errors were now.
    Perfect as usual.

Posting Permissions

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