Display text field in "numeric" order

[sonso]

I am doing a school application where "Spring" semester is 1, "Summer" semester is 2, and "Fall" semester is 3. I would like the order to display as:

Spring
Summer
Fall

I have created a Semester table with these entries with SemesterOrder as 1 through 3. I'd like to have the number enter in the table, but display the corresonding name (as you might do with day of week order vs. name of day). Any suggestions on how to do this?

 

[gusbrunston]

Hi:

Perhaps, if the control "txtSemesterNo" is bound to the semester number field; and
if "txtSemesterName" is an unbound control

In the OnCurrent event of the form enter:

If Me.SemesterNo = 1 Then
    Me.SemesterName = "Spring"
ElseIf
  Me.SemesterNo = 2 Then
    Me.SemesterName = "Summer"
ElseIf
  Me.SemesterNo = 3 Then
    Me.SemesterName = "Fall"
Else

'build an error handler in case
  'someone enters "4" or "5"

End If

Or, you could use the "Select Case Statement".

I hope this is helpful.



Gus Brunston An old PICKer, using Access2000.

 

[gusbrunston]

Hi:
Sorry, I changed names in midstream:

Me.SemesterNo should be Me.txtSemesterNo

Me.SemesterName should be Me.txtSemesterName

or whatever the appropriate control names are... Gus Brunston An old PICKer, using Access2000.

 

[sonso]

This is a possible solution, although it requires essentially having two fields to the work of one. This is a classic 1->1 database problem, using a number to represent a longer string, just displaying the string for information purposes. I did this successfully once with a combo box that queried the related table, and simply hid the first # column. This way the underlying data was the number and the name was displayed. I'm going to try this again.

 

[JeremyNYC]

If you're using a combo box to show this value, you can just have the numeric value in the query behind your form, sorted by that field (as well as what others you need it sorted by). Then look into making a two-column combo box that hides the first column.

If you're using a text box to display the Term, add both the numeric and text fields to the source query, again sorting by the numeric.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.