Traversing fields in current record.

[zz7]

Hi all,

My question is:

Having the following current record in a view/query (design view):

FIELDS: f1, f2, f3, f4
DATA : a b a c

I'd like to make an expression/temporary-field after field 4 that calls an
application module public function, let us say; "SelfCount(s as string)".

So, the fields in the query would be:

FIELDS: f1, f2, f3, f4, f5: SelfCount("a")

This function counts and returns the ocourrences of "a" in the fields
collection of the same current record from where it is evoqued.

Is this possible?
Any hints on this?

Thanks for your time,
Regards,
zz7

 

[Tom Wickerath]

Hi ZZ,

If you know that you will always have the same number of fields, you can set
up a query that uses a new union query as it's source of data. The union
query would look something like this:

qUniCountA

SELECT f1 AS MyField
FROM TableName
WHERE f1 = 'a'
UNION
SELECT f2 AS MyField
FROM TableName
WHERE f2 = 'a'
UNION
SELECT f3 AS MyField
FROM TableName
WHERE f3 = 'a'
UNION
SELECT f4 AS MyField
FROM TableName
WHERE f4 = 'a';

Then, a new query like this:

SELECT Count (*) FROM qUniCountA


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[zz7]

Thank you very much Tom.

Hi ZZ,

If you know that you will always have the same number of fields, you can
set
up a query that uses a new union query as it's source of data. The union
query would look something like this:

qUniCountA

SELECT f1 AS MyField
FROM TableName
WHERE f1 = 'a'
UNION
SELECT f2 AS MyField
FROM TableName
WHERE f2 = 'a'
UNION
SELECT f3 AS MyField
FROM TableName
WHERE f3 = 'a'
UNION
SELECT f4 AS MyField
FROM TableName
WHERE f4 = 'a';

Then, a new query like this:

SELECT Count (*) FROM qUniCountA


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________