Function used in SQL Select query

[Jean]

Hi all,

This problem has me dumbstruck at the moment and I was wondering if
someone could offer some advice while I have a go at it myself.

Background:
I have a query qryAll that takes data from numerous tables, and
produces the following data(Note: 1="Acme" and 2="Other" under column
Car1_Brand):

Name Car1_Brand Car1_Acme_Model Car1_Oth_Name Car1_Oth_Year
---- ---------- --------------- ------------ ---------
Smith 1 A123
Jones 2 Corvette 2000
Gates 1 A234
Dell 2 Mercedes 2004
So, if a person has "Acme" under Car1_Brand, then the next column
Car1_Acme_Model will give the model of the specific car made by Acme.
However, if she has other under Car1_Brand, then then name and year is
given in the last two columns.

What I have done so far is write a select query in my VB code which
uses a built-int function GetBrand to get the Car1_brand for each
person, in the following way:

strSelect = "SELECT GetBrand(Nz([qryAll.Car1_Brand])) AS CAR_1, "

NOTE: (GetBrand(integer) uses a simple Select Case to return a string
with the car type based on the integer supplied as parameter, 1="Acme"
and 2 ="Other")

Problem:
I want a column in my VB code's query for CAR_1_TYPE (i.e. something
with ...AS CAR_1_TYPE...). This column will look at the Car1_Brand
of each person, and when it is "Acme", it will display Car1_Acme_Model,
and when it is "Other", it will concatenate the two strings from
columns Car1_Oth_Name & Car1_Oth_Year. I wanted to write a simliar
function to GetBrand() above, but do not know how I should make it
always look at the current record, this is a bit confusing! So that my
VB code will look like this:

strSelect = strSelect & GetModel(Nz([qryAll.Car1_Acme_Model]),
Nz([Car1_Oth_Name]),
Nz(Nz([qryAll.Car1_Acme_Model]))

TIA