If QuoteRefNo is ever null or a zero-length string or contains any non-number
characters in the last 6, you should get an error and that may be the source of
the DataType Mismatch error.
You might try one of the following:
SELECT Min(CInt(Right$([JobQuote].[QuoteRefNo],6))) AS MinRef
from JobQuote
WHERE QuoteRefNo is Not Null
The Next will return 0 for nulls, empty strings, and strings that begin with a
letter. If there is a letter in the middle of the substring you will get the
value up to the letter (with some exceptions).
SELECT Min(Val(Right("0" & QuoteRefNo,6))) as MinRef
From JobQuote
Or perhaps the best - test in the where clause for QuoteRefNo that end in at
least 6 number characters. This is probably the least likely to return
erroneous data, but it will miss any records that have a bad QuoteRefNo such as
"AB00o023". Note that I typed in a lower case "oh" for clarity, while a user
could have type an upper case "oh" (O) vice a zero (0). Not much difference visibly.
SELECT Min(CInt(Right$([JobQuote].[QuoteRefNo],6))) AS MinRef
from JobQuote
WHERE QuoteRefNo LIKE "*######"
Hi all,
Can you please help me with a problem I'm having. I have the following
query that produces a 'data type mismatch' error...
SELECT Min(CInt(Right$([JobQuote].> o> ,6))) AS MinRef
from JobQuote
I'm selecting a QuoteRefNo (such as M000123), converting it to int
and trying to get the lowest.
Can anyone see what I'm doing wrong?
Thanks,
Richard.
