Is this Query inefficient?

[Mike Ridley]

Suppose I have a customer table ("customer") :-
Primary Key cust_id long integer
cust_name text(40)
cust_type text(1)

containing 80000 rows.
There are about 10000 rows where customer type = "A"
To retrieve these rows I use query

Select * from customer where cust_type = get_type()

But function get_type() gets its data using a lookup in a parameter
file and for this example will evaluate to "A"

function get_type() as string
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "select spa_param.pp_da ta from spa_param where
(spa_param.pp_k ey = 'CUSTTYPE');"
Set rs = db.OpenRecordse t(sqlstr)
If rs.RecordCount = 1 Then
rs.MoveFirst
get_type = (rs!pp_data)
Else
' do the error routine
get_type = ""
End If
rs.Close
Set rs = Nothing
Set db = Nothing
end function

My question is:-
Will get_type() be evaluated once and used in the query on the
customer table or will it be evaluated for each row that is read in
the customer table.

Thanks in advance

Mike

[Trevor Best]

Mike Ridley wrote:

Suppose I have a customer table ("customer") :-
Primary Key cust_id long integer
cust_name text(40)
cust_type text(1)

containing 80000 rows.
There are about 10000 rows where customer type = "A"
To retrieve these rows I use query

Select * from customer where cust_type = get_type()

But function get_type() gets its data using a lookup in a parameter
file and for this example will evaluate to "A"

function get_type() as string
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "select spa_param.pp_da ta from spa_param where
(spa_param.pp_k ey = 'CUSTTYPE');"
Set rs = db.OpenRecordse t(sqlstr)
If rs.RecordCount = 1 Then
rs.MoveFirst
get_type = (rs!pp_data)
Else
' do the error routine
get_type = ""
End If
rs.Close
Set rs = Nothing
Set db = Nothing
end function

My question is:-
Will get_type() be evaluated once and used in the query on the
customer table or will it be evaluated for each row that is read in
the customer table.

Thanks in advance

Mike

A function without parameters will be executed once for the whole query.
If you were to pass in a column name to the function call then it would
execute for each row. So in your case, there's no problem.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer