My clever function in SQL Select query

[Guest]

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 CarBrand):

Name CarBrand CarAcme_Model CarOth_Name CarOth_Year
---- ---------- ------------ -------- ------
Smith 1 A123
Jones 2 Corvette 2000
Gates 1 A234
Dell 2 Mercedes 2004

So, if a person has "Acme" under CarBrand, then the next
column CarAcme_Model will give the model of the specific car made by Acme.
However, if she has other under CarBrand, 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 CarBrand for each
person, in the following way:

strSelect = "SELECT GetBrand(Nz([qryAll.CarBrand])) AS CAR_1, "
(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 CarBrand
of each person, and when it is "Acme", it will display CarAcme_Model, and
when it is "Other", it will concatenate the two strings from columns
CarOth_Name & CarOth_Year. I wanted to write a simliar function to GetBrand()
above, but do not know I should mak 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.CarAcme_Model]),
Nz([CarOth_Name]),
Nz(Nz([qryAll.CarAcme_Model]))

TIA

Jean

 

[Michel Walsh]

Hi,



Something like:


SELECT Name,
CarBrand,
SWITCH( CarBrand="Acme", expression1,
CarBrand="That", expression2,
TRUE, expression3)
FROM myTable




where expression1 is what has to be returned if carBrand='Acme',
expression2 is carBrand='That', and expression3 in other cases.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi,

Thanks for your reply.

I actually wrote another function GetCarBrand(1, 2, 3) which takes variants
as paramters, and tests whether 1 is IsEmpty() - sufficing this it assigns
the "Acme" code to this car, othewise if not true then it will take the
parameters 2 and 3 and assign it to the car brand.

Regards,

Hi,



Something like:


SELECT Name,
CarBrand,
SWITCH( CarBrand="Acme", expression1,
CarBrand="That", expression2,
TRUE, expression3)
FROM myTable




where expression1 is what has to be returned if carBrand='Acme',
expression2 is carBrand='That', and expression3 in other cases.



Hoping it may help,
Vanderghast, Access MVP

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 CarBrand):

Name CarBrand CarAcme_Model CarOth_Name CarOth_Year
---- ---------- ------------ -------- ------
Smith 1 A123
Jones 2 Corvette 2000
Gates 1 A234
Dell 2 Mercedes 2004

So, if a person has "Acme" under CarBrand, then the next
column CarAcme_Model will give the model of the specific car made by Acme.
However, if she has other under CarBrand, 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 CarBrand for
each
person, in the following way:

strSelect = "SELECT GetBrand(Nz([qryAll.CarBrand])) AS CAR_1, "
(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 CarBrand
of each person, and when it is "Acme", it will display CarAcme_Model, and
when it is "Other", it will concatenate the two strings from columns
CarOth_Name & CarOth_Year. I wanted to write a simliar function to
GetBrand()
above, but do not know I should mak 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.CarAcme_Model]),
Nz([CarOth_Name]),
Nz(Nz([qryAll.CarAcme_Model]))

TIA

Jean