 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / March 2006record counter
am particularly dim this morning..... have simple table with field that is a Ref that varies: Ref 1 1 1 2 2 3 3 and I need to add a count-by-ref using query : Ref Count 1 1 1 2 1 3 2 1 2 2 3 1 5 1 5 2 it's not Sum, it's not Count..... adding a counter table with no join results in total count table for each ref.... just kind of dense on this at the moment....help!!  SignatureNTC Hello, in a general module, put the following code: '---------------- Dim gNum As Long, gRef As Long Function GetNum(pRef As Long) as long If pRef <> Nz(gRef) Then gNum = 1 gRef = pRef Else gNum = gNum + 1 End If GetNum = gNum End Function '---------------- then, the grid of query would be like this: field --> Ref field --> Count: GetNum([ref]) Have an awesome day Warm Regards, Crystal MVP Microsoft Access strive4peace2006 at yahoo.com > am particularly dim this morning..... have simple table with field that is a > Ref that varies: [quoted text clipped - 26 lines] > > just kind of dense on this at the moment....help!! thankyou for the effort but no, alas, I can't get this going because I receive a message: Undefined Function: 'GetNum' in expression I did name the Module GetNum and also double checked that the Function GetNum doesn't have a typo inside ....but the query doesn't recognize it so am not able to use this ..... SignatureNTC > Hello, > [quoted text clipped - 58 lines] > > > > just kind of dense on this at the moment....help!! perhaps you are putting the code in the wrong place... it goes in a general module, not a class module... from the database window (where you can choose Tables, Queries, Forms, Reports, Pages, Macros, Modules), click on Modules and then click the NEW button... THIS is where you need to put the code Have an awesome day Warm Regards, Crystal MVP Microsoft Access remote programming and training strive4peace2006 at yahoo.com > thankyou for the effort but no, alas, I can't get this going because I > receive a message: [quoted text clipped - 4 lines] > GetNum doesn't have a typo inside ....but the query doesn't recognize it so > am not able to use this ..... indeed - that is where it is. I never knew one could 'call' a module from a query calculation - - and it seems like a great idea - - but as of now it is not working.....maybe I will experiment with some other very simple modules and see if I can get them called in by a query - - because even outside this specific need - it would be a useful approach.... from a different pure SQL user forum someone gave me a pure sql statement that I can plug directly into the query - - and my rough test last night showed it to work. for which I am grateful. But I'm better at VB than SQL - so would still like to iron out your technique if I can...... although I never got to this point yet with your technique; what is not clear to me in using a module called in by a query is that the module references to values in the table need to be explicit i.e. with TableName.FieldName and it would seem that the module could be using values from any table/form/report. just fyi - a very interesting new venue to explore for me.....thanks SignatureNTC > perhaps you are putting the code in the wrong place... it > goes in a general module, not a class module... [quoted text clipped - 23 lines] > > GetNum doesn't have a typo inside ....but the query doesn't recognize it so > > am not able to use this ..... Pardon me, but you cannot name the module and the function the same. Try re-naming the module modGetNum. Modules and procedures (functions and subs) apparently share the same name space. I say "apparently" since I am guessing. But I do know that you cannot name a module the same as any function. > perhaps you are putting the code in the wrong place... it goes in a > general module, not a class module... [quoted text clipped - 23 lines] >> GetNum doesn't have a typo inside ....but the query doesn't recognize it >> so am not able to use this ..... hey thanks for that additional input - I modified the Module name to be different than the Function and the error disappeared and everything ran ok. the exact math calc I need isn't correct - - but I should be able to fine tune... thanks much to both as I never realized you could call a module into a query calc... SignatureNTC > Pardon me, but you cannot name the module and the function the same. Try > re-naming the module modGetNum. Modules and procedures (functions and subs) [quoted text clipped - 29 lines] > >> GetNum doesn't have a typo inside ....but the query doesn't recognize it > >> so am not able to use this ..... every time I use the slider up/down of the query result grid....the values in the the calculated column change !!! I never seen anything like it..... SignatureNTC > Pardon me, but you cannot name the module and the function the same. Try > re-naming the module modGetNum. Modules and procedures (functions and subs) [quoted text clipped - 29 lines] > >> GetNum doesn't have a typo inside ....but the query doesn't recognize it > >> so am not able to use this ..... that is because it is recalculating each time -- this solution assumes that you won't do that... If you want to see the number from the top of the list, you can look in the record selector box in the bottom left corner of the screen. Have an awesome day Warm Regards, Crystal MVP Microsoft Access remote programming and training strive4peace2006 at yahoo.com > every time I use the slider up/down of the query result grid....the values in > the the calculated column change !!! I never seen anything like it..... hello once more, I owe you the retraction of my prior statement that the math does not work quite right - - it works correctly. It was just that I had moved the up/down scroll bar and the results had changed - and I didn't realize it. I modified the query to a MakeTable query and so the table is of course not changing - and every thing works fine. So thanks much for this.... But I have never seen the phenomena of the query grid values re-calculating due to movement of its scroller bar....can you enlighten me on this?? SignatureNTC > that is because it is recalculating each time -- this > solution assumes that you won't do that... [quoted text clipped - 15 lines] > > every time I use the slider up/down of the query result grid....the values in > > the the calculated column change !!! I never seen anything like it..... you're welcome ;) I have noticed recalculation of "Record Number" fields done this way in queries -- a query is a dynamic set of data that is queried/calculated as you render to the screen. Queries do not store information, they get data from the tables Have an awesome day Warm Regards, Crystal MVP Microsoft Access remote programming and training strive4peace2006 at yahoo.com > hello once more, > [quoted text clipped - 7 lines] > But I have never seen the phenomena of the query grid values re-calculating > due to movement of its scroller bar....can you enlighten me on this?? |
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.