 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005count field data?
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 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 > Good Day all, > [quoted text clipped - 9 lines] > > Brook >Good Day all, > [quoted text clipped - 7 lines] > >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] 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 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 > hi, > [quoted text clipped - 31 lines] > > SUNIL.T Dear Brook: It looks like the COLORVALUE"" is the problem. Do you mean COLORVALUE = ""? Tom Ellison > Thank you for the post, > [quoted text clipped - 46 lines] >> >> SUNIL.T 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 Thanks for your suggestion... ONce I have the function, how do I call it within my query? Brook > hi, > [quoted text clipped - 16 lines] > > Sunil.t 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 > Thanks for your suggestion... > [quoted text clipped - 22 lines] > > > > Sunil.t 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 > hi, > [quoted text clipped - 43 lines] > > > > > > Sunil.t >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] 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: > > [quoted text clipped - 15 lines] > > John W. Vinson[MVP] >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] 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 ...? [quoted text clipped - 12 lines] > > John W. Vinson[MVP] >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: [quoted text clipped - 3 lines] > >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] 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: [quoted text clipped - 21 lines] > > John W. Vinson[MVP] 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.