Want to show differnt text in combo or list box then whats stored in table

[P3ndrag0n]

I'm sure the title wasn't very clear so hopefully I can explain my issue a little easier. Let's assume I'm new'ish to ACCESS.

What I would like to do is use a combo or list box (or a better suggestion if you have one) to make it easier for my end user to enter information that makes sense to them but is stored as a different format in the table.

Basically in my table I have certain periods of the year stored as follows:

201202
201205
201208

Those equate to the following:


Spring Semester 2012
Summer Semester 2012
Fall Semester 2012

In the form, I want the end user to pick Spring Semester 2012, and in the table it will enter 201202.

Basically my end user will not know the codes (201202,201205 etc) but will be familiar with the semester time frames.

Keep in mind, that I will have 6-8 different categories in the form that will need the same setup. If that makes a difference.

Can someone help?

Thanks!

[orange]

Assuming you're new'ish to Access, do you have a clear statement in plain English of WHAT you are trying to do?
Do you have an overview data model of the things involved in your proposed database, and how those things relate to each other? Combo and listboxes are control types on forms, but you haven't mentioned that in detail.

Access and other databases have a variety of Datatypes to simplify processing.
Data bases work best when you work with the 1 fact 1 field concept.

Good luck with your project.

[P3ndrag0n]

I'm sorry if my initial answer wasn't plain enough for you, but let me try again.

I want to control what a user can put into a field in a form. (Ive done this with list boxes and combo boxes so that's why I've mentioned them.)

I want the user to see these choices in the field they are entering:

Spring Semester 2012
Summer Semester 2012
Fall Semester 2012

If the user picks any of those, I want it's corresponding "code" entered into the table. So if they pick Spring Semester 2012, then I want the number 201202 entered into the table. (not Spring Semester 2012)

Is this possible?

[orange]

Yes, build a reference table, and use the reference table with appropriate relationships to your other tables to resolve queries.
Build your tables, 1 fact 1 field for flexibility. You can concatenate fields as you wish for display.
tblSemesterReference
SemesterRefID autonumber PK
SemesterName text

with (storage) values like

01
Spring Semester 2012
02
Summer Semester 2012
etc,

but you could go 1 step further

semID PK
SemName
SemYear

01
Spring Semester
2012

And for display 2012 Spring Semester or Semester -2012 - Spring or whatever you want.

Get your tables and relationships designed to meet your business requirements. Then test the model to make sure it works. Then build the database.

Good luck.

[P3ndrag0n]

Yes, build a reference table, and use the reference table with appropriate relationships to your other tables to resolve queries.
Build your tables, 1 fact 1 field for flexibility. You can concatenate fields as you wish for display.
tblSemesterReference
SemesterRefID autonumber PK
SemesterName text

with (storage) values like

01
Spring Semester 2012
02
Summer Semester 2012
etc,

but you could go 1 step further

semID PK
SemName
SemYear

01
Spring Semester
2012

And for display 2012 Spring Semester or Semester -2012 - Spring or whatever you want.

Get your tables and relationships designed to meet your business requirements. Then test the model to make sure it works. Then build the database.

Good luck.

Thanks!

This is exactly what I had done but could not get to work, even though I'd done something very similar with other combo boxes in my form. After reading through your example I went back and reviewed my relationships and realized I was making the autonumber in my reference the PK then I had a second column with the Semester Ref ID (201202) and a third column with the text. Once I eliminated the autonumber and made the Semester Ref ID my primary, then everything worked as intended.

Long story short, thanks for your help!