Change Sequence of Characters

[Leona Leal Educator]

This is my first time, so hope you guys give me a break.

Most of my programming is done with IBM AS 400's.

I need to change the sequence of Characters in my Time Zone Field.

What I Have is T = Canadian Eastern Time Zone
E = U.S. Eastern Time Zone
C = U.S. Central Time Zone
M = U.S. Mountain Time Zone
P = U.S. Pacific Time Zone
A = Alaskain Time Zone
H = Hawaiian Time Zone
etc.

I want my Query to display the results by actual time or in the following
Sequence.

T, E, C, M, P, A, H, and N instead of A, C, E, H, M, N, P, T.

How is this accomplished with Microsoft.

Thanks in Advance

Grandma L

 

[John Spencer]

If you want a special sort order you can create a table with two fields.
This is usually the best solution since it is flexible and easily
maintained. If you need to add new items to the sort order or change
the sort order, you modify the table. This automatically takes care of
changes throughout the database where you use this

TZone - T,E,C etc
TheOrder - 1, 2, 3 etc

Include that table in your query and join it to the time zone field.

Another possibility is to use an expression and sort on the expression
This expression will return 0 to 8 (0 = No match and 1 to 8 the position
of the letter). You can sort by the expression.

Instr(1,"TECMPAHN",[Time Zone Field])

If you are going to use the expression, it is often a good idea to
create a small VBA function to do so. Then if you make a change to the
function it will work throughout the database

Public Function fSortTime(strTimeZone) as Integer
Dim iReturn as integer

If Len(strTimeZone & "") = 0 then
fSortTime = 999 'or zero depending on your sort requirements
Else
iReturn = Instr(1,"TECMPAHN",inStr)
If iReturn = 0 Then
fSortTime = 999 'or whatever value
Else
fSortTime = iReturn
End If
End If

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Allen Browne]

Create a table in Access to handle this data for you.

The table will have fields like this:
TimeZoneID Text primary key. (Contains the T, E, etc)
MinutesOffset Number number of minutes offset from GMT.

The last field would contain -480 for a timezine that is 8 hours behind
Greenwich. (I suggest minutes, as the integer math works better for
timezones that have half hours.

You can now JOIN this table to whatever else you have that contains the T,
E, C, etc codes.

Then if you wanted to convert a date/time field named MyDT to the GMT value,
you would use:
DateAdd("n", [MinutesOffset], [MyDT])

You can also sort by the MinutesOffset field to display the timezones in
order.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...