By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Using part of a field to fill in another field?

P: n/a
I am making an inventory database. I have a field InventoryNumber
where I put a specific code. The code always starts with a particular
letter. I would like to fill in another field with specific
information based on what that letter is. Can somebody show me how to
do this? Thanks in advance.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hmmm...there are a lot of ways that you can do this. It depends on how
many possibilities you have for that first letter.

If there are lots of possibilities OR if you expect the list to grow,
you could create a table that holds the list of your 'specific
information' and a letter ID that corresponds to the 1st letter in your
InventoryNumber field. I am assuming that each letter corresponds to a
unique value for the desired information, such as A = Apples, B =
Bananas, etc. and that you don't also have A = Apricots. If that's a
valid assumption, create a table with two fields in it: AlphaID
(primary key, 1 character long) and Description (text). Enter in all
your items into the table. Since this would be a table with a maximum
of 26 entries, you could then use a
DLookup("Description","tblDescriptions","AlphaID = '" &
Left(InventoryNumber,1) & "'" in a form or a query to grab the
description that belongs to the 1st letter of your InventoryNumber.

If there are only a few possibilities AND you DON'T expect the list to
be changing, you can create a custom function using the Select Case
statement in VB. It would be something like this:

Public Function GetDescription(InventoryNumber As String) As String
Select Case Left(InventoryNumber, 1)
Case "A"
GetDescription = "Apples"
Case "B"
GetDescription = "Bananas"
Case "C"
GetDescription = "Cantaloupe"
Case Else
GetDescription = "Invalid Letter in Inventory Number"
End Select
End Function

You could then use that function anywhere you'd like to get a
description.

HTH,
Jana

Nov 13 '05 #2

P: n/a
I decided to do it like this and it works great. Thanks for the help.

Private Sub InventoryNumber_AfterUpdate()
If InventoryNumber Like "d*" Then
FolderName = "D Items"
Folder_Code = "640079"
End If
If InventoryNumber Like "j*" Then
FolderName = "J Items"
Folder_Code = "640061"
End If
If InventoryNumber Like "g*" Then
FolderName = "G Items"
Folder_Code = "640372"
End If
If InventoryNumber Like "M*" Then
FolderName = "M Items"
Folder_Code = "640060"
End If
End Sub

Nov 13 '05 #3

P: n/a
Just out of curiosity, what do you do when it doesn't start with D, J,
G or M?

Dec 7 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.