You are here:

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.
So this:
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: 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.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.