If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change Sequence of Characters
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 |
#2
|
|||
|
|||
Change Sequence of Characters
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 '================================================= === Leona Leal Educator wrote: 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 |
#3
|
|||
|
|||
Change Sequence of Characters
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Leona Leal Educator" Leona Leal wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|