 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / May 2006HELP - Random numbering & Random ASCII
Hi Everyone! I have a table that I've created and I'm trying to do an append query to an existing table...all my fields match the existing table. One of my fields "ItemNo" looks like this... CRIA3DA000 My question is... Can someone show me how to write the code to generate the middle 4 characters for this "ItemNo" field so I don't have to manually enter this for 13000+ records. *the CRI is always the first 3 characters *the 000 is always the last 3 characters... so I only need the middle 4 characters to output randomly which can include NUMERIC and ALPHA but NO symbols, punctuations, etc and NO DUPLICATES. Any help would be appreciated. Also could you keep in mind that I'm a NEWBIE when writing code. Thanks - Crystal This one was fun to do! Put a calculated field in your query that calls this function. It generates the numbers based on your rules. It checks to see if the number already exists in your tabe and does not use it if it is already in the table. In the DLookup, I used then name ItemNo for your field name. Since I don't know your table name, you will have to change "MyTableNameHere" to the name of the table you are appending to. Public Function MakeProdNumber() As String Dim strProdNumber As String Dim intIndex As Integer Do While True Randomize Do While Len(strProdNumber) < 4 intIndex = ((36 * Rnd) + 1) strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C", "D", "E", _ "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", _ "T", "U", "V", "W", "X", "Y", "Z", "1", "2", "3", "4", "5", "6", _ "7", "8", "9", "0") Loop If IsNull(DLookup, "[ItemNo]", "MyTableNameHere", "[ItemNo] = '" _ & strProdNumber & "'") Then Exit Do Else strProdNumber = "" End If Loop MakeProdNumber = "CRI" & strProdNumber & "000" End Function Post back if you have questions > Hi Everyone! > [quoted text clipped - 15 lines] > > Thanks - Crystal Hi Klatuu - Could you tell me what the DLookup means. Thanks - Crystal > This one was fun to do! > Put a calculated field in your query that calls this function. It generates [quoted text clipped - 52 lines] > > > > Thanks - Crystal You can get more detail in VBA Help, but basically, it looks for a value in your database based on the criteria you give it. The first argument is the name of the field you want to get the value from. The second is the name of the domain (a table or query) the field is in. The last is the criteria to select a record. In this case, we will look for the ItemNo. We don't care what it is, but the function requires a field name. We are looking for the new ProductNumber we just generated. The DLookup function will return a Null value if it can't find a record based on the criteria. If it finds a record, it returns the value in the field identified in the first argument. Since we are checking to make sure the number we have created is unique, we are looking in the table to see if it already exists. If a Null value is returned, we know it is unique, so we return the number to the query which uses the new number to append to the table. If we don't get a Null returned, it means the number is already used, so we discard it and try another one. Hope that clears it up for you. > Hi Klatuu - > Could you tell me what the DLookup means. [quoted text clipped - 57 lines] > > > > > > Thanks - Crystal Hi Klatuu - Okay I used your code and put a calculated field in my query to call the function...it works...however it's creating the same number for every record I have in that field. I need it to create a random (unique) ItemNo for each record (13,000). Is this possible? Sorry for not being more specific. Thanks - Crystal > You can get more detail in VBA Help, but basically, it looks for a value in > your database based on the criteria you give it. [quoted text clipped - 76 lines] > > > > > > > > Thanks - Crystal I tested the function and it returns a different number each time it is run. Where are you calling it from? > Hi Klatuu - > [quoted text clipped - 85 lines] > > > > > > > > > > Thanks - Crystal Here's what it looks like... ItemNo Description NewItemNo SRT-1- CRIOUXH000 TB-1- CRIOUXH000 -19- CRIOUXH000 -30- CRIOUXH000 -101- CRIOUXH000 -103- CRIOUXH000 -107- CRIOUXH000 I have 13,000+ different Descriptions and I need a different ItemNo for each one. Crystal P.S. Thanks for responding so quickly > I tested the function and it returns a different number each time it is run. > Where are you calling it from? [quoted text clipped - 88 lines] > > > > > > > > > > > > Thanks - Crystal I understand what you want. As I said before, I tested the function and it returns a different number each time I call it. To be able to help, I need to know where you are calling it from. If you are calling it using a calculated field in your query, it is possible it is only running one time. I can't remember why that happens, but I have seen it before. Open the module where the funtion is in design view. Put your cursor on the first line of executable code in the function. Press F9 to set a break point. run your query. It should go into debug and highlight the line you set the break point on. Click on Debug, Run to Cursor. See if it stops on the next record. If it does not, then we need to find out what the problem is. One other question. Are you building a new table or updating records in an existing table? > Here's what it looks like... > [quoted text clipped - 105 lines] > > > > > > > > > > > > > > Thanks - Crystal I'm trying to update an existing table. Here's what the code looks like in it's entirety Public Function MakeProdNumber() As String Dim strProdNumber As String Dim intIndex As Integer Do While True Randomize Do While Len(strProdNumber) < 4 intIndex = ((36 * Rnd) + 1) strProdNumber = strProdNumber & Choose(intIndex, "A", "B", "C", "D", "E", _ "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", _ "U", "V", "W", "X", "Y", "Z", _ "1", "2", "3", "4", "5", "6", "7", "8", "9", "0") Loop If IsNull(DLookup("[ItemNo]", "tbl_CRI", "[ItemNo] = '" _ & strProdNumber & "'")) Then Exit Do Else strProdNumber = "" End If Loop MakeProdNumber = "CRI" & strProdNumber & "000" End Function I put my cursor on the Do While True, F9, Debug, Run to Cursor...it highlites in yellow and doesn't move to next line. Your probably pulling your hair out with me...LOL C > I understand what you want. As I said before, I tested the function and it > returns a different number each time I call it. [quoted text clipped - 121 lines] > > > > > > > > > > > > > > > > Thanks - Crystal You just need to make a slight change to the function. Require an argument - you don't need to use it. In the query, call the function with any field as an argument. I'm not sure that the function will work as advertised, as I didn't bench test it. Public Function MakeProdNumber(vAnything) As String ... Update query call Field: FieldToUpdate Update To: MakeProdNumber([AnyField-Even-fieldtoUpdate]) >I understand what you want. As I said before, I tested the function and it > returns a different number each time I call it. [quoted text clipped - 162 lines] >> > > > > > > >> > > > > > > Thanks - Crystal Thanks, John. That validates what I thought the problem was. I missed that point in my original design. The function does work as advertised. > You just need to make a slight change to the function. Require an > argument - you don't need to use it. [quoted text clipped - 176 lines] > >> > > > > > > > >> > > > > > > Thanks - Crystal Klatuu - So I put John's suggestion (below) following the End Function of your code? Public Function MakeProdNumber(vAnything) As String End Function Then I do an UPDATE Query Field: ItemNo Table: tbl_CRI Update to: MakeProdNumber([vAnything-Even-fieldtoUpdate]) You said to pick any field I have in my table...StockNo, Description, ShortDescr...can you tell me what John means by [vAnything-Even-ItemNo] TY - C > Thanks, John. That validates what I thought the problem was. I missed that > point in my original design. [quoted text clipped - 180 lines] > > >> > > > > > > > > >> > > > > > > Thanks - Crystal He means what I said, just any field. My apologies, I had forgotten about having to have an argument in a function for it to execute for each row in the query. Going home now, let me know how it works out. > Klatuu - > So I put John's suggestion (below) following the End Function of your code? [quoted text clipped - 195 lines] > > > >> > > > > > > > > > >> > > > > > > Thanks - Crystal IT WORKS...SWEET!!!! THANKS SO MUCH > He means what I said, just any field. My apologies, I had forgotten about > having to have an argument in a function for it to execute for each row in [quoted text clipped - 201 lines] > > > > >> > > > > > > > > > > >> > > > > > > Thanks - Crystal Thanks for letting me know. Glad to help. > IT WORKS...SWEET!!!! > THANKS SO MUCH [quoted text clipped - 204 lines] > > > > > >> > > > > > > > > > > > >> > > > > > > Thanks - Crystal IT WORKS...SWEET!!!! THANKS SO MUCH > You just need to make a slight change to the function. Require an > argument - you don't need to use it. [quoted text clipped - 176 lines] > >> > > > > > > > >> > > > > > > Thanks - Crystal Hi Klatuu - Your code works great and your right it does generate a different number each time you run the application. Please see my earlier response to you...my apologies for not being more specific earlier. Thanks - C > I tested the function and it returns a different number each time it is run. > Where are you calling it from? [quoted text clipped - 88 lines] > > > > > > > > > > > > Thanks - Crystal Sorry, you did describe what you are doing. I had forgotten. In my foggy memory I think this has to do with not passing an argument to the query. I don't know if this will work or not, but if you are willing to experiment a bit, we can make it work. Let's try adding an argument first. I don't think we need to do anything with it. So where you call the function, pick any field and put it in as the argument like this: MakeProductNumber([AnyField]) Then in the code for the function, Public Function MakeProdNumber(IgnoreMe as ???) As String ??? should be the same data type as the field you pick to be the argument. Let me know what happens. (don't forget to wear protective googles and hearing protection) > Hi Klatuu - > [quoted text clipped - 96 lines] > > > > > > > > > > > > > > Thanks - Crystal > Hi Everyone! > [quoted text clipped - 16 lines] > > Thanks - Crystal I have a function that returns a random alphanumeric string of any given length, but requirement that there be no duplicates means that you don't really want a random string at all. I can think of three ways to go about this: 1. Use a function that first generates the pseudo-random key, then checks to see if it already exists in the table, and generates another one if it does. With only 4 characters in the "random" string. the odds are pretty good that you're going to get some duplicates. -- OR -- 2. Don't bother checking for duplicates as the keys are generated, just generate them and try to add the records in an append query. Some records will probably be rejected for duplicate key errors. Write a second query that selects just the records that didn't get added, generates new keys for them, and tries again to add them. Repeat until all records have been successfully added. -- OR -- 3. Run the query to generate all the keys in a work table. Examine that table for duplicates, and regenerate keys for those records that are dups. Then append from the work table to the live table.  SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) You, know, I wasn't doing the math. with 130,000 records and requiring a unique string of only 4 characters, it should not really be a problem. Given 36 possible characters to enter into a 4 character string, that would be 36^ 4 which is not quite 1.7 million possibillities. > > Hi Everyone! > > [quoted text clipped - 41 lines] > table for duplicates, and regenerate keys for those records that are > dups. Then append from the work table to the live table. > You, know, I wasn't doing the math. with 130,000 records and > requiring a unique string of only 4 characters, it should not really > be a problem. Given 36 possible characters to enter into a 4 > character string, that would be 36^ 4 which is not quite 1.7 million > possibillities. I make that roughly 8% probability of a duplicate, if it's 130K records and not 13K. 8% isn't that great (though it seems to have worked for the OP), but .8% is not bad at all. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) It was 130K (from the OP) so I don't have to manually enter this for 13000+ records. Just under 8% is what I calculated. She responded that is worked for her. My original concern, more that whether there were enough combinations to go around, was performance. I did the math because I got concerned about how many disc fetches would be required to get a unique combination. > > You, know, I wasn't doing the math. with 130,000 records and > > requiring a unique string of only 4 characters, it should not really [quoted text clipped - 5 lines] > and not 13K. 8% isn't that great (though it seems to have worked for > the OP), but .8% is not bad at all. > It was 130K > (from the OP) > so I don't have to manually enter this for 13000+ records. 13000 = 13,000 = 13K > Just under 8% is what I calculated. > She responded that is worked for her. Right, so all is well. > My original concern, more that whether there were enough combinations > to go around, was performance. I did the math because I got > concerned about how many disc fetches would be required to get a > unique combination. I was concerned about performance, too. That's why I proposed alternate versions, including approaches that would "shoot first, and check for duplicates later". I think we were both aware of the potential issues, though it appears I was needlessly concerned about the risk of duplicates, given that it's a one-time import. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) yeah, a one time import, but I wonder if she has considered keeping a version of that function around to add new records to the database. If she will be doing it one at a time, it shouldn't be a problem for a long time. If she is doing batches and the database grows significantly, at some point in time, performance will suffer because the probability of hitting a duplicate goes up. > > It was 130K > > (from the OP) [quoted text clipped - 17 lines] > though it appears I was needlessly concerned about the risk of > duplicates, given that it's a one-time import. > yeah, a one time import, but I wonder if she has considered keeping a > version of that function around to add new records to the database. > If she will be doing it one at a time, it shouldn't be a problem for > a long time. If she is doing batches and the database grows > significantly, at some point in time, performance will suffer because > the probability of hitting a duplicate goes up. I agree. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
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.