Indexed Expression

[Ryan]

I have a query that I have an expression that Concatenate's
DatabaseBatch:[Database] &" "& [Batch#]. I have another form that looks for
records based on the FullName. The problem is that the search is starting to
take a long time. I tried indexing the [FirstName] and [LastName] columns in
the table, but that didnt help. Is there a way to index in the query itself,
or a way to speed up the search in another way? It takes about 18-20 seconds
to search, where as it used to only take about 1 second when there were not
very many records. Here is the code for the events.

Private Sub QuickDatabaseBatchGo_Click()
On Error GoTo Err_QuickDatabaseBatchGo_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BatchTrackingEntryForm"

stLinkCriteria = "[DatabaseBatch]=" & "'" & Me![QuickDatabaseBatch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_QuickDatabaseBatchGo_Click:
Exit Sub

Err_QuickDatabaseBatchGo_Click:
MsgBox Err.Description
Resume Exit_QuickDatabaseBatchGo_Click

End Sub
--

 

[Michel Walsh]

Indexes don't work on expression, even


IndexedField + 0


won't use the index, with Jet. So, if possible, make your criteria on
[Database] and on [Batch#] independently:


stLinkCriteria = "Database=" & databasePart & " AND [Batch#] =" &
batchNumberPart

rather than

stLinkCriteria = "DatabaseBatch=" &
concatenationBetweenDatabaseNameAndBatchNumber


Sure, you may have to split what is in your form control to get the database
part and the batch number part, but that should solve your time execution
problem (with an index on each separate field, eventually).

Note that you probably need to use delimiters if database is a string:


stLinkCriteria = "Database=""" & databasePart & """ AND [Batch#] =" &
batchNumberPart


Vanderghast, Access MVP