Using MS Access/In a form - building a value from some textboxes
In Access, in a form, I would like to "build" the value that will be entered into the table from a set of textboxes individually.
For example: DVD Length field should be formatted as "27:13 min" or "1:15:13 min" This one can be solved with an input mask, partly. But what I would like to have three text boxes on the form (one each for hours, minutes, and seconds) and then from those three, build a string that follows the format.
Hours: |1| Minutes: |15| Seconds: |13|
enters the string "1:15:13 min" into the Length field in the table
The second is the Media Number. We label our DVDs by 1- or 2-letter code (AM for Automotive, B for Business, etc), a 1-4 digit number, and an optional (A,B,C etc) at the end. For example: "H 11A" "G 1009" "P 15" With one textbox for code, one for number, one for suffix.
Label Code: |H| Number |11| Suffix(Optional): |A|
is entered into the MediaNumber field as "H 0011 A"
**IS* this even possible, and if so, how would I do this?
Yes its possible. The former is OK, the latter I question. More on that.
For selecting a time I would suggest you take a look at the time pickers in the sample found here: http://www.utteraccess.com/forum/index.php?showtopic=1901319&hl=Time+pieces
This can do what you want to capture a time and store it in a field. However, you can do this with separate controls and assign the value to another control using the TimeSerial function.
For the second part, as a rule each field in your tables should be a finite piece of information. So if your Media number is composed 2 or 3 parts, they should be stored as separate fields. You can display them together using an expression. This is especially true if the number is supposed to be sequentially assigned (see my blog on sequential numbering). So I would have 3 fields: MediaTypeCode, MediaNumber, MediaSuffix. Then you display thenm using the expression:
=MediaTypeCode & " " & MediaNumber & Nz(MediaSuffix,"")
The Nz will accomdate if no suffix was entered. You can use that expression wherever you need to display the full Media Identifier.