DataType Mismatch in query criteria (XP SP2)

Topic

New Reply

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

Reply | Quote

Replies

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.)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote