Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Data Mismatch Error (2002)

    Hi,

    I'm running a query that I've run many times before with no issues. Today I'm getting a data type mismatch error. I "think" I've narrowed it down to being a problem with "dischargedLOS" and the formula that is in it. It's a formula that I was given by this site and has worked beautifully till today. I'm now out of my bag of tricks to figure out what is going on.

    Thanks,
    Leesha

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

    Re: Data Mismatch Error (2002)

    You have some records that contain " - -" in the dis_dt field. This can't be converted to a valid date. What would you want to do with these?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Mismatch Error (2002)

    Hi Hans,

    I suppose I could convert them to a bogus date like 1/1/1800 or it could be null and then revise the query based on that. I'll give it a try and if I can't make it work I'll let you know. I "should" be able to fix this now that I know what is causing the error. I couldn't for the life of me figure out why it was blowing up all of a sudden.

    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Mismatch Error (2002)

    OK, I finally worked through all the issues with that last query and there many as a result of the "- - ". The info is imported from an excel file that is run from another database so I have not control over what the user exports out. I made all the changes and now I'm getting a similar error in a query that uses qryMERInitial. I checked for blanks or issues like you found on the last one but don't see any. As usual I'm lost. The query that is giving me trouble is qryLOS.

    Thanks!
    Leesha

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

    Re: Data Mismatch Error (2002)

    You have defined DischargedLOS as

    IIf([activestatus]="active","0",IIf([ActiveStatus]="Discharged",DateDiff("d",CDate([soc_dt]),CDate([dis_dt]))))

    "0" is a string (text) value, while DateDiff returns a number value. This causes confusion. Change the definition to

    IIf([activestatus]="active",0,IIf([ActiveStatus]="Discharged",DateDiff("d",CDate([soc_dt]),CDate([dis_dt]))))

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Mismatch Error (2002)

    Thank you!!! I would never have found that one.

    Leesha

Posting Permissions

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