a very slow SQL query

[Guest]

Hello,
I have an access project with a Table containing about 3000 records, and
growing. The table contains an Id field. The Id is a number which always
starts with the digits 1 or 2.
I want to find the record with the largest Id starting with a specific digit
in the table (for example, the record with the largest Id, from between the
records which have an Id starting with the digit 1).
I am using the following SQL query (where ComputerLetter is the digit I want
the Id to start with and keyfield is the Id field):
subSQL = "SELECT * FROM " & tableName & " t2 " & _
" WHERE (t2." & keyField & " LIKE '" & ComputerLetter & "%') " & _
" AND (len(t2." & keyField & ") > len(" & tableName & "." &
keyField & "))"
getMaxId_SQL = _
"SELECT max(" & tableName & "." & keyField & ") AS maxId" & _
" FROM " & tableName & _
" WHERE " & _
" (" & tableName & "." & keyField & _
" LIKE '" & ComputerLetter & "%')" & _
" AND NOT EXISTS (" & subSQL & ")"

After that I use a function which returns the first value (denoted maxId) of
the first column, returned by the SQL query statement, and then I do:
pureMaxId = Val(Mid(maxId, 2, Len(maxId) - 1))

The point is that when debugging the code line by line, the part which takes
very long is the running of the SQL query.
any ideas why and how to make it run faster?
thank you.

 

[Jeff Boyce]

I'm not sure I follow...

Are you saying you want to find the Max(ID) Where Left(ID,1)=1?

Jeff Boyce
<Access MVP>

 

[Guest]

exactly, but then I want to find the next free Id starting with the digit 1
(to give it to a new record). So if, for example, I found thath the max(ID)
Where Left(ID,1) is 1999, then the next Id given should be 10000, and not
2000.

 

[Guest]

The thing is, that if I try using a regular "SELECt Max(ID) Where Left(ID,
1)=1" query, it doesn't work. I think it's because since the Id field in the
table is a String field, and not an integer field (and it has to stay that
way since there are some old records in the table which have an Id containing
letters).
Therfore, if I use this kind of query, 199 is considered greater than 1965
(lexicographic order)...

 

[Jeff Boyce]

I'm not following what you are trying to do. What "algorithm" are you
applying to go from "1999" to "10000" in a single step?

Is there a chance that the "1" (or "2") preceding the ?sequence number is
confusing the issue? Are you saying that you have TWO fields, one of them
either "1" or "2", and the second holding a sequence number? If so, you
could treat them as two separate fields, not as one combined field. For
display purposes, you can use a query to "re-assemble" them.