Modify A Query

[Guest]

I use this query:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27;

I would like to change this so that the query will prompt me to enter a 2
character code, and then filter down to the records that match
Mid([ISIN],10,2).

Thank you in advance.

 

[Tom Ellison]

Dear Carl:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27
WHERE Mid(ISIN, 10, 2) = [Enter Code: ]

Any success with this?

Tom Ellison

 

[John Spencer]

Dear Tom,

Curiousity question. Would using Like be faster in this situation. -
avoiding multiple calls to the Mid function?

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27
WHERE ISIN Like "?????????" & [Enter Code: ] & "*"

And if the user didn't need the calculated NSCCode that would be even
faster.

On small recordsets, I'm sure you would not see much difference in speed,
but on large recordsets you might see a fairly large difference. Or am I
over-analyzing this

Spencer

Dear Carl:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27
WHERE Mid(ISIN, 10, 2) = [Enter Code: ]

Any success with this?

Tom Ellison

I use this query:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27;

I would like to change this so that the query will prompt me to enter a 2
character code, and then filter down to the records that match
Mid([ISIN],10,2).

Thank you in advance.

 

[Tom Ellison]

Dear John,

Here's the way I look at performance.

If it may save a disk access, experiment and learn what's best, and apply
that.

If it involves processing time only, it's probably not worth thinking about.
You'd need a stopwatch calibrated in microsecnds to find must differences
like this.

The above is opinion. I won't charge you anything if you'd like to set up
your own test and try to find a difference. I'm betting you won't find any
difference, but I'd like to hear.

Undoubtedly there would be a difference. Except for testing, you'd be
speculating on how it is coded if you theorize which is faster. My guess on
that would be that the MID function is faster. Maybe not. So, go get
Microsofts source code and add up the instruction times, or perform the
testing. My guess is you won't find a difference, but if you do, that Mid()
is very slightly faster. Write your doctoral thesis on what you find! "How
I saved 7 microseconds on a 2 second query." That's what I'm expecting!

Tom Ellison

Dear Tom,

Curiousity question. Would using Like be faster in this situation. -
avoiding multiple calls to the Mid function?

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27
WHERE ISIN Like "?????????" & [Enter Code: ] & "*"

And if the user didn't need the calculated NSCCode that would be even
faster.

On small recordsets, I'm sure you would not see much difference in speed,
but on large recordsets you might see a fairly large difference. Or am I
over-analyzing this

Spencer

Dear Carl:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27
WHERE Mid(ISIN, 10, 2) = [Enter Code: ]

Any success with this?

Tom Ellison

I use this query:

SELECT Sept27.*, Mid([ISIN],10,2) AS NSCCode
FROM Sept27;

I would like to change this so that the query will prompt me to enter a
2
character code, and then filter down to the records that match
Mid([ISIN],10,2).

Thank you in advance.