Selection query with MAX(textfield) and WHERE textfield LIKE 'mask

[Carsten]

I have an ACCESS DB on which an ADODB query doesnt work.

the query should return the maximum value of a textfield by:

SELECT MAX(textfield) AS Maxnr
FROM table
WHERE textfield >= 'startvalue' AND textfield <= 'stopvalue' AND
LEN(textfield) = 7 and textfield LIKE 'I08____';

(the mask is 'i08' and 4times _ in ADO --> In access i changed the _
underscores into ? (questionsmarks). but the query return a null record,
because somehow now record matches the WHERE clauses.

When i remove the LIKE part, the query runs ok. but the LIKE-part is needed.

In SQL Server it runs perfectly (adodb query).

Are there some limitation in ACCESS? limitations in combinatie with LIKE and
MAX?? OR is there any other idea why this query does run in ADODB or ACCESS
when i have less than 2501 matching records. When i go above 2500 it doesnt
return the expected value. SQL hasnt this problem.

Any ideas why this query doesnt return what i expect?

greetings
Carsten

 

[Dale Fye]

Have you tried?

SELECT MAX(textfield) AS Maxnr
FROM table
WHERE textfield >= 'startvalue'
AND textfield <= 'stopvalue'
AND LEN(textfield) = 7
AND textfield LIKE 'I08*'

The wildcard character for JET is ? for a single character or * for multiple
characters.

HTH
Dale

 

[Carsten]

I had a mind about that, but as you mention it is multiple characters.
I use a limited start/stopvalue like IYY0001(start, where YY = 08 from 2008)
and IYY9999 (stopvalue, YY same as in start). The 0001 to 9999 is a numerical
part, which is auto increment by my code.

Therefor not the * (multiple) but 4 times _. A numerical wildcard characters
doesnt exist in ACCESS in my opinion.

Can you limit * to 4 characters??

The option with Like 'I08*' does work, but is not the best for our solution.
I work with limited count of numbers of the numerical part.

Carsten
==============================

 

[Bob Barrows]

I had a mind about that, but as you mention it is multiple characters.
I use a limited start/stopvalue like IYY0001(start, where YY = 08
from 2008) and IYY9999 (stopvalue, YY same as in start). The 0001 to
9999 is a numerical part, which is auto increment by my code.

Therefor not the * (multiple) but 4 times _. A numerical wildcard
characters doesnt exist in ACCESS in my opinion.

Can you limit * to 4 characters??

The option with Like 'I08*' does work, but is not the best for our
solution. I work with limited count of numbers of the numerical part.

That's 4 single characters isn't it?

like '108????'

 

[Carsten]

Yep, four characters, in my case 4 numerical characters [0->9].
but the numerical part of the mask is limited. ADODB query: LIKE 'I08____'
(4 underscores) in ACCESS: LIKE 'I08????'.

Its strange that is works with less then 2501 records (query runs and
returns good value), but above 2500 records queried from the WHERE clause,
the WHERE clause combination with SELECT MAX returns a null record.

Can there be table limitations or relationships which block this?
As far is i know there are very less relationships in my database. Mostly
done by coding.

I use VB6.0 with ADODB.recordset to run the given query.

 

[Bob Barrows]

Yep, four characters, in my case 4 numerical characters [0->9].
but the numerical part of the mask is limited. ADODB query: LIKE
'I08____' (4 underscores) in ACCESS: LIKE 'I08????'.

When querying ANY database via ADO, you must use the ODBC wildcards (%
and _)

It is only when using Access to run the queries (or DAO) that you need
to use the Jet wildcards (* and ?)

Its strange that is works with less then 2501 records (query runs and
returns good value), but above 2500 records queried from the WHERE
clause, the WHERE clause combination with SELECT MAX returns a null
record.

Can there be table limitations or relationships which block this? None that I am aware of.
As far is i know there are very less relationships in my database.

I can't quite parse that sentence.


Without access to your data, I do not believe I can offer a solution. I
am bogged down with my own work right now, otherwise I would offer to
take a look at it.