count field data?

[Guest]

Good Day all,

I have a qry that I use to track my inventory, and for each inventory item
there is a field called colours. Which can contain anywhere from 1 to 15
colours separated by a comma.. I.E. (50, 123, 57, 98) are the colours for one
particular item. Is there a way that I can setup an expression to cound the
colours, eliminating the commas?

Any suggestions are greatly appreciated!

Brook

 

[Tom Ellison]

Dear Brook:

I suggest you write a Public VBA function to do this. Use InStr to locate
the commas, counting them in a loop.

Tom Ellison

 

[John Vinson]

Good Day all,

I have a qry that I use to track my inventory, and for each inventory item
there is a field called colours. Which can contain anywhere from 1 to 15
colours separated by a comma.. I.E. (50, 123, 57, 98) are the colours for one
particular item. Is there a way that I can setup an expression to cound the
colours, eliminating the commas?

Any suggestions are greatly appreciated!

Brook

Not easily. This is one reason (among many) why you should not store
multiple values in on field - if you have a one to many relationship,
model it as a one to many relationship, in two tables.

You'll need to write VBA code to parse the text string and count
tokens. The Split() function should work with a bit of effort... let's
see...

yep... Ubound(Split([colors], ",")) + 1 will work.

John W. Vinson[MVP]

 

[Guest]

hi,

u can add the below function in ur database under modules...

Function find_comma(datavalue As String) As Integer

Dim i As Integer
Dim count As Integer
count = 0
If datavalue = Null Or datavalue = "" Then
find_comma = 0
Else
For i = 1 To Len(datavalue)
If Mid(datavalue, i, 1) = "," Then
count = count + 1
End If
Next i
find_comma = count + 1
End If

End Function


and in ur query call this function as

SELECT IIf(isnull(COLORVALUE)=True Or
COLORVALUE"",0,find_comma(TABLENAME!COLORVALUE)) AS Expr1, *
FROM TABLENAME;


I HOPE THIS WILL SOLVE UR PROBLEM...

PSL LET ME KNOW IF IT HELPS U

THANKS

SUNIL.T

 

[Guest]

hi,

u can also use the below function also

Function find_comma(datavalue As String) As Integer

Dim a() As String
count = 0
a = Split(datavalue, ",")
find_comma = UBound(a) + 1

End Function


and call this function in ur query


thanx

with regds

Sunil.t

 

[Guest]

Thanks for your suggestion...

ONce I have the function, how do I call it within my query?

Brook

 

[Guest]

Thank you for the post,

I added the module and then added the Select code to a new colomn in my
query, but am getting an error: The sytax of the subquery in this expression
is incorrect.

SELECT IIf(isnull(COLORVALUE)=True Or
COLORVALUE"",0,find_comma(tbldesigndata!COLORVALUE)) AS Expr1, *FROM
tbldesigndata)

Any suggestions?

Brook

 

[Guest]

hi,

u can call the function as specified in the query...

SELECT IIf(isnull(COLORVALUE)=True Or
COLORVALUE"",0,find_comma(TABLENAME!COLORVALUE)) AS Expr1, *
FROM TABLENAME;

ie in query developer window in "Field" row , right click and click build..a
new window called expression builder will come up...from that u click
functions->user defined functions then u will get the function u created in
module....


pls let me knowif this helps u

Thanx

With regards

Sunil.T

 

[Guest]

I tried that:

SELECT IIf(isnull(colours)=True Or
colours"",0,find_comma(tbldesigndata!colours)) AS Expr1, * FROM
tbldesigndata;

and am getting the syntax error mentioned in another post...

Brook

 

[John Vinson]

I tried that:

SELECT IIf(isnull(colours)=True Or
colours"",0,find_comma(tbldesigndata!colours)) AS Expr1, * FROM
tbldesigndata;

I think Sunil meant to say

Or colours = ""

If you don't have the Colours field set with "Allow Zero Length
Strings" in table design view, it's probably not necessary: the
simpler expression

IIf(IsNull([Colours], 0, FindComma([Colours])

in a vacant Query cell will work.

John W. Vinson[MVP]

 

[Tom Ellison]

Dear Brook:

It looks like the COLORVALUE"" is the problem. Do you mean COLORVALUE = ""?

Tom Ellison

 

[Guest]

Thanks for the response....

I tried the fix but am still getting the error message

SELECT IIf(isnull(colours)=True Or colours=
"",0,find_comma(tbldesigndata!colours)) AS Expr1, * FROM tbldesigndata;

You mentioned a shorted expression to use, can I use that in place of this
one that I cannot get to work ...?

Thanks,

BRook

I tried that:

SELECT IIf(isnull(colours)=True Or
colours"",0,find_comma(tbldesigndata!colours)) AS Expr1, * FROM
tbldesigndata;

I think Sunil meant to say

Or colours = ""

If you don't have the Colours field set with "Allow Zero Length
Strings" in table design view, it's probably not necessary: the
simpler expression

IIf(IsNull([Colours], 0, FindComma([Colours])

in a vacant Query cell will work.

John W. Vinson[MVP]

 

[John Vinson]

You mentioned a shorted expression to use, can I use that in place of this
one that I cannot get to work ...?

The ! may be the problem. Try

SELECT IIf(isnull(colours),0,find_comma(colours)) AS Expr1, * FROM
tbldesigndata;

assuming that tbldesigndata in fact has a field named [Colours], and
that you have copied and pasted Sunil's VBA code into a Module,
compiled it, and saved it.

Note that you must not use find_comma as the name of the Module - the
modules and the procedures within modules must have different names.

John W. Vinson[MVP]

 

[Guest]

i'm sorry for so many problems with this.... I am still gettting the error...
do you think it could be that the field "Colours" consist of:

Colours: [Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose
Colours]

do you think this could be the issue?

When I get the error and click ok it goes directly to the "SELECT" part of
the clause?

Brook

You mentioned a shorted expression to use, can I use that in place of this
one that I cannot get to work ...?

The ! may be the problem. Try

SELECT IIf(isnull(colours),0,find_comma(colours)) AS Expr1, * FROM
tbldesigndata;

assuming that tbldesigndata in fact has a field named [Colours], and
that you have copied and pasted Sunil's VBA code into a Module,
compiled it, and saved it.

Note that you must not use find_comma as the name of the Module - the
modules and the procedures within modules must have different names.

John W. Vinson[MVP]

 

[John Vinson]

i'm sorry for so many problems with this.... I am still gettting the error...
do you think it could be that the field "Colours" consist of:

Colours: [Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose
Colours]

do you think this could be the issue?

Ummmm...

Yes.

It would have helped a lot if, as requested, you had posted the
structure of your table.

You'll need to pass this same concatenation string to the VBA
function, instead of passing Colours.

When I get the error and click ok it goes directly to the "SELECT" part of
the clause?

That's because you can't reuse the calculated field Colours in a
second expression (your function call).

John W. Vinson[MVP]

 

[Guest]

My apologies...

thanks for all your help!

Brook

i'm sorry for so many problems with this.... I am still gettting the error...
do you think it could be that the field "Colours" consist of:

Colours: [Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose
Colours]

do you think this could be the issue?

Ummmm...

Yes.

It would have helped a lot if, as requested, you had posted the
structure of your table.

You'll need to pass this same concatenation string to the VBA
function, instead of passing Colours.

When I get the error and click ok it goes directly to the "SELECT" part of
the clause?

That's because you can't reuse the calculated field Colours in a
second expression (your function call).

John W. Vinson[MVP]

 

[Guest]

hi,

i run the below query here and it worked fine....

SELECT IIf(isnull(colours),0,find_comma(colours)) AS Expr1, * FROM
tbldesigndata;

or

SELECT IIf(isnull( tbldesigndata.colours),0,find_comma(
tbldesigndata.colours)) AS Expr1, * FROM tbldesigndata;

and dont forget to place that function in ur module...it should work.....

EG:

Table:
=====

ID AMT dt
1 100 11,22,2222
2 200 1,2,3,4,5,6
1 100 2,2,2,2,2,2,2,2
3 400 0,0,9999,99
3 200 2
4 1000


Result of above Query
=================
id count_of_comma dt
1 1 11,22,2222
1 1 2,2,2,2,2,2,2,2
2 1 1,2,3,4,5,6
3 1 0,0,9999,99
3 1 2
4 0

thanx

With regds

Sunil.T