Question field to field, get only uppercase? (1 Viewer)

chaoticm · Apr 7, 2011

Firstly, sorry if this is posted in the wrong area. I am just a n00b. Literally my first post, and my 1st access project.

I am building an inventory list for my business and I am trying to generate a unique 'ItemID' field.
I want to get only the uppercase letters from my BandName field and a random 5 digit number to make up the ItemID field.

How do I do this. I have been trying for days. lol

Galaxiom · Apr 7, 2011

This will extract the Upper case letters to a variable called Code.

Code:

BandNameLength = Len(Me.Bandname)
 
For n = 1 To BandNameLength
   NameLetter =  Mid(Me.BandName, n, 1)
   NameAscii = Asc(NameLetter)
   If NameAscii >= 65 And NameAscii <= 90 Then
      Code = Code & NameLetter
   End If
Next

The random number is not such a good idea because you could still hit the same number twice.

Use a sequence by finding the largest number already used in the group and adding one. This can be done with a DMax and the topic is covered extensively on this forum.

Several post also explain why you wouldn't use this code as the Primary Key field.

John Big Booty · Apr 7, 2011

Welcome to the forum.

You can use check if the ASC() value of a character is between 65 and 90 inclusive to see if it is an upper case character.

You will need to loop through your text field using some code that might look something like;

Code:

Dim intCnt as Integer
Dim intTxtCnt as Integer

intTxtCnt = Len(Me.YourBrandNameField)
intCnt = 1

While intCnt >= intTxtCnt 

If [URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL](Me.YourBrandNameField, intCnt, 1) >= 65 And  Mid(Me.YourBrandNameField, intCnt, 1) <= 90 Then
     Me.YourItemID = Me.YourItemID & Mid(Me.YourBrandNameField, intCnt, 1)
End If

intCnt = intCnt +1

Wend

Me.YourItemID = Me.YourItemID & [URL="http://www.techonthenet.com/access/functions/numeric/format.php"]Format[/URL](1 + Int( 10000 * [URL="http://www.techonthenet.com/access/functions/numeric/rnd.php"]Rnd[/URL]()), "00000")

However I would strongly counsel you against using this as a unique ID or Primary Key for your records, instead let the AutoNumber function do this for you behind the scenes. This is not to say that you can't use the above scheme for creating part numbers for your own use, just don't use it as a Primary key.

John Big Booty · Apr 7, 2011

Too slow

Question field to field, get only uppercase? (1 Viewer)

chaoticm

New member

Galaxiom

Super Moderator

John Big Booty

AWF VIP

John Big Booty

AWF VIP

Similar threads

Users who are viewing this thread