Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DataType Mismatch in query criteria (XP SP2)

    Rebooted, Compact/Repaired, copied objects over into fresh database, rebooted again, moved to an XP machine, everything failed -- what am I doing wrong? It must be something simple!

    (Edits -- forgot to add that this database was built on a Win 2k machine, but I also moved it to an XP machine -- because there was some date issue in Access 2k which I seem to remember bit me once in Access XP on a 2k machine, although this does not involve a date ...)

    Here's the problem: I'm trying to prompt the user with the max tracking number + 1 as a suggestion for a new record. The "TrackNo" is in the form: "12-2003."

    To test this out, I had one TrackNo that was null, one "abc," one "174," and one "11-204," as well as several in the correct format.

    Step1Qry:
    SELECT TrackNo as GoodTracker
    FROM MyDB
    WHERE TrackNo Is Not Null; -- this excludes the one null

    Step2Qry:
    SELECT Val([GoodTracker]) AS NumTrkr
    FROM Step1Qry; -- this yields 0 for the "abc"

    Step3Qry:
    SELECT NumTrkr ( Edited: Removed the "SB_Year" from original forum msg -- had earlier pared everything out except the very most basic fields required.)
    FROM Step2Qry
    WHERE NumTrkr > 0; -- attempting to exclude the "0" record

    Step3Qry fails with DataType Mismatch on Criteria expression.

    * I did a "vartype" function on the NumTrkr, and it is a double. Just to see if it would make a difference, I converted the NumTrkr to a CLng, then did the last comparison -- still broke!

    Note: This formerly was just a number, indexed / no dups, without the "- year" section. The users wanted to restart the number at each new year, so I changed it to the current format and made it a string. I thought that would take a little extra work on the queries, but really offer no problem.

    thx
    Pat

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

    Re: DataType Mismatch in query criteria (XP SP2)

    I'm not sure why this happens; perhaps the query optimizer tries to apply the criteria to the original data. It's probably best to clean up the TrackNo field, so that it contains only valid, non-null entries. You should have no problem then (and no need for all these intermediate queries.)

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DataType Mismatch in query criteria (XP SP2)

    Thanks, Hans. I resolved the way you suggested. It is just very wierd. I remade the queries all over again just in case there was some other problem -- starting with the first "pare out the nulls" query, which just seems to be ignored in the final query (or, there is some other issue ...) Anyway, now resolved, now I'll have to figure out the potential validation that I'll need. I wish I'd never joined the counter and the year fields into a character tracking #, I might return to those for all the "max" stuff and the validation.
    Thanks!
    Pat

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

    Re: DataType Mismatch in query criteria (XP SP2)

    If you assign the value of TrackNo for a new record in code, you can lock the TrackNo control for editing. There is no need for the user to edit this field, so you wouldn't have to add validation. But I agree that separate counter and year fields are easier to handle.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DataType Mismatch in query criteria (XP SP2)

    True, I would like to do that, but they do want the ability to edit if desired. I create a default "next available number" as a "suggestion," and the field is locked -- but may be unlocked (msg pops up when fld is clicked) for editing. I do have an autonum record counter behind everything, which connects to the other linked tables, so I don' t have to hassle beyond just getting this number right, and making sure it is unique.
    Thanks, Hans.
    Pat

Posting Permissions

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