Printable View
Hello.
I'm new in database Acces and need help.
There is a posibility to count numbers from a memo?
I have a table named Doubles, with 2 fields. First field is a name and a second is a memo with numbers separated by comma: 52,152,154,55,55,156,59,60,63,63 for example. I need to count how many numbers using sql query.
Thanks.
off hand not sure about counting the number of commas - it is such an odd request I don't have a method in my near term memory and you will want to Bing/Google about for a method to count specific characters in a string. Assuming you have that count then you can determine the quantity of numbers.
if you find a method post it here and then I can assist on fine tuning it.
Thanks CahabaData for your reply. I searched google but none relevant info. Will search again more deep and if find a method will pos it.
Using just a query you can not count the numbers, but, you can count the numbers from a memo field using a query and a module. The query will call a Function in the module, this function will then step through the data passed to the function and count the numbers. The number count is then passed back and displayed in the query.
is there a method in vba to count a specific character in a string, such as a comma ... ? I use Len, Left, Right, Mid methods all the time to manipulate strings but offhand can't think of a method for counting a unique character;......or would one have to build a custom loop?
You will need to use LEN and build a loop and using INSTR to find the first comma, then add 1 to your counter use INSTR again but move the start point
of the to the next position after the comma, Repeat this process until the end of the memo.
I have attached a zip 2013 version of the database that will count the numbers. The query passes the memo field to the module. The module manipulates memo field to perform the number count.
If you cannot open the database I will post the code on the forum.
loopy indeed. didn't think there was a VBA method to count a specific character... for this same effort, more or less, one really might be better off building a normalized 'child' relational table - using a variation of this logic to write out the string into records with a cross reference key back to the main table.... that sort of depends on whether there is new data being made constantly in this text mode or whether this is a 1 time effort to set up a new db from an import or something.
You could use the table with the memo field as your source table then refresh your main table by deleting the main table and writing each number as a record in the main table. This will ensure the main table has any new data, BUT, be aware once the data is deleted in the main table all data is lost and any data that was in the main table and not in the source table will be lost.
You will need to think this through do you update the main table or use a temp table then append the temp table to your main table. My preference is to have a temp table with records for each number and look at updating the main table. Using a temp table any data errors can be rectified e.g. there maybe a NULL memo field before refreshing the main table.
What are you trying to achieve from the number count?
Hello Allan.
The story is i'm tryind to build a application for my personal collection.
I collect wrappers from chewing gum and right now using excel. Some time ago i found Longtion Application Builder, a tool to build database application. Started to learn the tool to build a database app instead using excel.
I need to count numbers from a memo for doubles in collection. I want to enter the string with commas in memo and want the app to return how many doubles (numbers) i have from a specific serie. The problem is i cannot use VBA, like the code you build. Sorry i forgot to tell that. Meanwhile i'm in a business trip and do not have much time to spend but will search more to fing a solution. Maybe will change the way i enter the double numbers. I'll post the solution if found one.
You will need two tables one for your wrappers and one for your collections.
Your wrappers table will contain information your wrapper details, the collections table will contain the wrapper name and the number. You may require additional fields in both tables as required,
Now, using the collections table you now create a query with the fields wrapper name and the number. In the query design view on the Design menu of the toolbar there is an item called Totals. This will allow you to count or sum the number of each wrapper name.
Have look at this link http://www.fontstuff.com/access/acctut04.htm.
I have sent you a private message if you require further assistance.
Allan