Breaking my head - TimeZone Conversion

[Mike]

Hi everyone,

Here's the skinny: I have a data base that has events and dates in GMT/UTC
in different locations around the world. I am required to change these time
stamps to the local time, and also take into account DST... I've found a
pretty simple way of doing it, but I'm concerend about memory usage, and
speed of the function I've created. I was wondering if there is a slicker
more efficient way of doing this... I'm still concidered a noob...

Here is the partially finished code so far:

Function correctDST(UserLocation As String, TimeAction As Date)

Dim TimeGMTSub(108, 2)
Dim TimeGMTAdd(159, 2)
Dim TimeOffset(23) As Integer
Dim SearchTimeGMT As Integer
Dim DSTStart As Date
Dim DSTEnd As Date

' Set the DST start and end time
DSTStart = #3/11/2007 2:00:00 AM#
DSTEnd = #11/4/2007 2:00:00 AM#

' Populate the TimeCounter Array
For TimeCounter = 0 To 23
TimeOffset(TimeCounter) = TimeCounter 'TimeSerial(TimeCounter, 0, 0)
Next

' Populate the Time Zones that need to be subtracted from GMT
TimeGMTSub(0, 0) = "ALBANY"
TimeGMTSub(0, 1) = TimeOffset(5) 'Normal
TimeGMTSub(0, 2) = TimeOffset(4) 'Daylight Savings
TimeGMTSub(1, 0) = "ALBUQUERQUE"
TimeGMTSub(1, 1) = TimeOffset(7)
TimeGMTSub(1, 2) = TimeOffset(6)
TimeGMTSub(2, 0) = "ASUNCION"
TimeGMTSub(2, 1) = ""
TimeGMTSub(2, 2) = ""
TimeGMTSub(3, 0) = "ATLANTA"
TimeGMTSub(3, 1) = ""
TimeGMTSub(3, 2) = ""
TimeGMTSub(4, 0) = "AUSTIN"
TimeGMTSub(4, 1) = ""
TimeGMTSub(4, 2) = ""
TimeGMTSub(5, 0) = "BALTIMORE"
TimeGMTSub(5, 1) = ""
TimeGMTSub(5, 2) = ""
TimeGMTSub(6, 0) = "BARRIE"
TimeGMTSub(6, 1) = ""
TimeGMTSub(6, 2) = ""
TimeGMTSub(7, 0) = "BATON ROUGE"
TimeGMTSub(7, 1) = ""
TimeGMTSub(7, 2) = ""
TimeGMTSub(8, 0) = "BEDFORD-US"
TimeGMTSub(8, 1) = ""
TimeGMTSub(8, 2) = ""
TimeGMTSub(9, 0) = "BELLEVILLE"
TimeGMTSub(9, 1) = ""
TimeGMTSub(9, 2) = ""
TimeGMTSub(10, 0) = "BELO HORIZONTE"
TimeGMTSub(10, 1) = ""
TimeGMTSub(10, 2) = ""
TimeGMTSub(11, 0) = "BENTONVILLE"
TimeGMTSub(11, 1) = ""
TimeGMTSub(11, 2) = ""
TimeGMTSub(12, 0) = "BIRMINGHAM"
TimeGMTSub(12, 1) = ""
TimeGMTSub(12, 2) = ""
TimeGMTSub(13, 0) = "BOSTON"
TimeGMTSub(13, 1) = ""
TimeGMTSub(13, 2) = ""
TimeGMTSub(14, 0) = "BRASILIA"
TimeGMTSub(14, 1) = ""
TimeGMTSub(14, 2) = ""
TimeGMTSub(15, 0) = "BUENOS AIRES"
TimeGMTSub(15, 1) = ""
TimeGMTSub(15, 2) = ""
TimeGMTSub(16, 0) = "BUFFALO"
TimeGMTSub(16, 1) = ""
TimeGMTSub(16, 2) = ""
TimeGMTSub(17, 0) = "CALGARY"
TimeGMTSub(17, 1) = ""
TimeGMTSub(17, 2) = ""
TimeGMTSub(18, 0) = "CAMPINAS"
TimeGMTSub(18, 1) = ""
TimeGMTSub(18, 2) = ""
TimeGMTSub(19, 0) = "CARACAS"
TimeGMTSub(19, 1) = ""
TimeGMTSub(19, 2) = ""
TimeGMTSub(20, 0) = "CHARLOTTE"
TimeGMTSub(20, 1) = ""
TimeGMTSub(20, 2) = ""
TimeGMTSub(21, 0) = "CHARLSTON"
TimeGMTSub(21, 1) = ""
TimeGMTSub(21, 2) = ""
TimeGMTSub(22, 0) = "CHICAGO"
TimeGMTSub(22, 1) = ""
TimeGMTSub(22, 2) = ""
TimeGMTSub(23, 0) = "CINCINNATI"
TimeGMTSub(23, 1) = ""
TimeGMTSub(23, 2) = ""
TimeGMTSub(24, 0) = "CLEVELAND"
TimeGMTSub(24, 1) = ""
TimeGMTSub(24, 2) = ""
TimeGMTSub(25, 0) = "COLUMBUS"
TimeGMTSub(25, 1) = ""
TimeGMTSub(25, 2) = ""
TimeGMTSub(26, 0) = "DALLAS"
TimeGMTSub(26, 1) = ""
TimeGMTSub(26, 2) = ""
TimeGMTSub(27, 0) = "DENVER"
TimeGMTSub(27, 1) = ""
TimeGMTSub(27, 2) = ""
TimeGMTSub(28, 0) = "DES MOINES"
TimeGMTSub(28, 1) = ""
TimeGMTSub(28, 2) = ""
TimeGMTSub(29, 0) = "DETROIT"
TimeGMTSub(29, 1) = ""
TimeGMTSub(29, 2) = ""
TimeGMTSub(30, 0) = "EDMONTON"
TimeGMTSub(30, 1) = ""
TimeGMTSub(30, 2) = ""
TimeGMTSub(31, 0) = "GARZA GARCIA"
TimeGMTSub(31, 1) = ""
TimeGMTSub(31, 2) = ""
TimeGMTSub(32, 0) = "GRAND RAPIDS"
TimeGMTSub(32, 1) = ""
TimeGMTSub(32, 2) = ""
TimeGMTSub(33, 0) = "GREENVILLE"
TimeGMTSub(33, 1) = ""
TimeGMTSub(33, 2) = ""
TimeGMTSub(34, 0) = "GUATEMALA CITY"
TimeGMTSub(34, 1) = ""
TimeGMTSub(34, 2) = ""
TimeGMTSub(35, 0) = "GUAYAQUIL"
TimeGMTSub(35, 1) = ""
TimeGMTSub(35, 2) = ""
TimeGMTSub(36, 0) = "GUAYNABO"
TimeGMTSub(36, 1) = ""
TimeGMTSub(36, 2) = ""
TimeGMTSub(37, 0) = "HALIFAX"
TimeGMTSub(37, 1) = ""
TimeGMTSub(37, 2) = ""
TimeGMTSub(38, 0) = "HARTFORD"
TimeGMTSub(38, 1) = ""
TimeGMTSub(38, 2) = ""
TimeGMTSub(39, 0) = "HONOLULU"
TimeGMTSub(39, 1) = ""
TimeGMTSub(39, 2) = ""
TimeGMTSub(40, 0) = "HOUSTON"
TimeGMTSub(40, 1) = ""
TimeGMTSub(40, 2) = ""
TimeGMTSub(41, 0) = "INDIANAPOLIS"
TimeGMTSub(41, 1) = ""
TimeGMTSub(41, 2) = ""
TimeGMTSub(42, 0) = "JACKSON"
TimeGMTSub(42, 1) = ""
TimeGMTSub(42, 2) = ""
TimeGMTSub(43, 0) = "JACKSONVILLE"
TimeGMTSub(43, 1) = ""
TimeGMTSub(43, 2) = ""
TimeGMTSub(44, 0) = "KANSAS CITY"
TimeGMTSub(44, 1) = ""
TimeGMTSub(44, 2) = ""
TimeGMTSub(45, 0) = "LAS VEGAS"
TimeGMTSub(45, 1) = ""
TimeGMTSub(45, 2) = ""
TimeGMTSub(46, 0) = "LAURENCE HARBOR"
TimeGMTSub(46, 1) = ""
TimeGMTSub(46, 2) = ""
TimeGMTSub(47, 0) = "LITTLE ROCK"
TimeGMTSub(47, 1) = ""
TimeGMTSub(47, 2) = ""
TimeGMTSub(48, 0) = "LONDON-CA"
TimeGMTSub(48, 1) = ""
TimeGMTSub(48, 2) = ""
TimeGMTSub(49, 0) = "LONG ISLAND"
TimeGMTSub(49, 1) = ""
TimeGMTSub(49, 2) = ""
TimeGMTSub(50, 0) = "LOS ANGELES"
TimeGMTSub(50, 1) = ""
TimeGMTSub(50, 2) = ""
TimeGMTSub(51, 0) = "LOUISVILLE"
TimeGMTSub(51, 1) = ""
TimeGMTSub(51, 2) = ""
TimeGMTSub(52, 0) = "MATAWAN"
TimeGMTSub(52, 1) = ""
TimeGMTSub(52, 2) = ""
TimeGMTSub(53, 0) = "MEDLEY"
TimeGMTSub(53, 1) = ""
TimeGMTSub(53, 2) = ""
TimeGMTSub(54, 0) = "MEMPHIS"
TimeGMTSub(54, 1) = ""
TimeGMTSub(54, 2) = ""
TimeGMTSub(55, 0) = "MEXICO CITY"
TimeGMTSub(55, 1) = ""
TimeGMTSub(55, 2) = ""
TimeGMTSub(56, 0) = "MIAMI"
TimeGMTSub(56, 1) = ""
TimeGMTSub(56, 2) = ""
TimeGMTSub(57, 0) = "MILWAUKEE"
TimeGMTSub(57, 1) = ""
TimeGMTSub(57, 2) = ""
TimeGMTSub(58, 0) = "MINNEAPOLIS/ST PAUL"
TimeGMTSub(58, 1) = ""
TimeGMTSub(58, 2) = ""
TimeGMTSub(59, 0) = "MONTEVIDEO-UY"
TimeGMTSub(59, 1) = ""
TimeGMTSub(59, 2) = ""
TimeGMTSub(60, 0) = "MONTREAL"
TimeGMTSub(60, 1) = ""
TimeGMTSub(60, 2) = ""
TimeGMTSub(61, 0) = "NASHVILLE"
TimeGMTSub(61, 1) = ""
TimeGMTSub(61, 2) = ""
TimeGMTSub(62, 0) = "NEW HAVEN"
TimeGMTSub(62, 1) = ""
TimeGMTSub(62, 2) = ""
TimeGMTSub(63, 0) = "NEW ORLEANS"
TimeGMTSub(63, 1) = ""
TimeGMTSub(63, 2) = ""
TimeGMTSub(64, 0) = "NEW YORK CITY"
TimeGMTSub(64, 1) = ""
TimeGMTSub(64, 2) = ""
TimeGMTSub(65, 0) = "NEWARK"
TimeGMTSub(65, 1) = ""
TimeGMTSub(65, 2) = ""
TimeGMTSub(66, 0) = "NORFOLK"
TimeGMTSub(66, 1) = ""
TimeGMTSub(66, 2) = ""
TimeGMTSub(67, 0) = "OMAHA"
TimeGMTSub(67, 1) = ""
TimeGMTSub(67, 2) = ""
TimeGMTSub(68, 0) = "ORLANDO"
TimeGMTSub(68, 1) = ""
TimeGMTSub(68, 2) = ""
TimeGMTSub(69, 0) = "OTTAWA"
TimeGMTSub(69, 1) = ""
TimeGMTSub(69, 2) = ""
TimeGMTSub(70, 0) = "PARSIPPANY"
TimeGMTSub(70, 1) = ""
TimeGMTSub(70, 2) = ""
TimeGMTSub(71, 0) = "PHILADELPHIA"
TimeGMTSub(71, 1) = ""
TimeGMTSub(71, 2) = ""
TimeGMTSub(72, 0) = "PHOENIX"
TimeGMTSub(72, 1) = ""
TimeGMTSub(72, 2) = ""
TimeGMTSub(73, 0) = "PITTSBURGH"
TimeGMTSub(73, 1) = ""
TimeGMTSub(73, 2) = ""
TimeGMTSub(74, 0) = "PORT ALEGRE"
TimeGMTSub(74, 1) = ""
TimeGMTSub(74, 2) = ""
TimeGMTSub(75, 0) = "PORT OF SPAIN"
TimeGMTSub(75, 1) = ""
TimeGMTSub(75, 2) = ""
TimeGMTSub(76, 0) = "PORTLAND-ME"
TimeGMTSub(76, 1) = ""
TimeGMTSub(76, 2) = ""
TimeGMTSub(77, 0) = "PORTLAND-OR"
TimeGMTSub(77, 1) = ""
TimeGMTSub(77, 2) = ""
TimeGMTSub(78, 0) = "QUITO"
TimeGMTSub(78, 1) = ""
TimeGMTSub(78, 2) = ""
TimeGMTSub(79, 0) = "REGINA"
TimeGMTSub(79, 1) = ""
TimeGMTSub(79, 2) = ""
TimeGMTSub(80, 0) = "RENO"
TimeGMTSub(80, 1) = ""
TimeGMTSub(80, 2) = ""
TimeGMTSub(81, 0) = "RESEARCH TRIANGLE PARK"
TimeGMTSub(81, 1) = ""
TimeGMTSub(81, 2) = ""
TimeGMTSub(82, 0) = "RICHMOND"
TimeGMTSub(82, 1) = ""
TimeGMTSub(82, 2) = ""
TimeGMTSub(83, 0) = "RIO DE JANEIRO"
TimeGMTSub(83, 1) = ""
TimeGMTSub(83, 2) = ""
TimeGMTSub(84, 0) = "ROCHESTER"
TimeGMTSub(84, 1) = ""
TimeGMTSub(84, 2) = ""
TimeGMTSub(85, 0) = "SACRAMENTO"
TimeGMTSub(85, 1) = ""
TimeGMTSub(85, 2) = ""
TimeGMTSub(86, 0) = "SAINT JOHN"
TimeGMTSub(86, 1) = ""
TimeGMTSub(86, 2) = ""
TimeGMTSub(87, 0) = "SALT LAKE CITY"
TimeGMTSub(87, 1) = ""
TimeGMTSub(87, 2) = ""
TimeGMTSub(88, 0) = "SAN ANTONIO"
TimeGMTSub(88, 1) = ""
TimeGMTSub(88, 2) = ""
TimeGMTSub(89, 0) = "SAN FRANCISCO"
TimeGMTSub(89, 1) = ""
TimeGMTSub(89, 2) = ""
TimeGMTSub(90, 0) = "SAN ISIDRO"
TimeGMTSub(90, 1) = ""
TimeGMTSub(90, 2) = ""
TimeGMTSub(91, 0) = "SAN JOSE"
TimeGMTSub(91, 1) = ""
TimeGMTSub(91, 2) = ""
TimeGMTSub(92, 0) = "SANTA FE DE BOGOTA-CO"
TimeGMTSub(92, 1) = ""
TimeGMTSub(92, 2) = ""
TimeGMTSub(93, 0) = "SANTIAGO"
TimeGMTSub(93, 1) = ""
TimeGMTSub(93, 2) = ""
TimeGMTSub(94, 0) = "SAO PAULO"
TimeGMTSub(94, 1) = ""
TimeGMTSub(94, 2) = ""
TimeGMTSub(95, 0) = "SEATTLE"
TimeGMTSub(95, 1) = ""
TimeGMTSub(95, 2) = ""
TimeGMTSub(96, 0) = "SILICON VALLEY"
TimeGMTSub(96, 1) = ""
TimeGMTSub(96, 2) = ""
TimeGMTSub(97, 0) = "SIOUX FALLS"
TimeGMTSub(97, 1) = ""
TimeGMTSub(97, 2) = ""
TimeGMTSub(98, 0) = "SPIRIT LAKE"
TimeGMTSub(98, 1) = ""
TimeGMTSub(98, 2) = ""
TimeGMTSub(99, 0) = "ST LOUIS"
TimeGMTSub(99, 1) = ""
TimeGMTSub(99, 2) = ""
TimeGMTSub(100, 0) = "TALLAHASSEE"
TimeGMTSub(100, 1) = ""
TimeGMTSub(100, 2) = ""
TimeGMTSub(101, 0) = "TAMPA"
TimeGMTSub(101, 1) = ""
TimeGMTSub(101, 2) = ""
TimeGMTSub(102, 0) = "TORONTO"
TimeGMTSub(102, 1) = ""
TimeGMTSub(102, 2) = ""
TimeGMTSub(103, 0) = "TULSA"
TimeGMTSub(103, 1) = ""
TimeGMTSub(103, 2) = ""
TimeGMTSub(104, 0) = "VANCOUVER"
TimeGMTSub(104, 1) = ""
TimeGMTSub(104, 2) = ""
TimeGMTSub(105, 0) = "WASHINGTON DC"
TimeGMTSub(105, 1) = ""
TimeGMTSub(105, 2) = ""
TimeGMTSub(106, 0) = "WILMINGTON"
TimeGMTSub(106, 1) = ""
TimeGMTSub(106, 2) = ""
TimeGMTSub(107, 0) = "WINNIPEG"
TimeGMTSub(107, 1) = ""
TimeGMTSub(107, 2) = ""

' Populate the Time Zones that need to be added to GMT
TimeGMTAdd(0, 0) = "AMSTERDAM"
TimeGMTAdd(1, 0) = "ANGOLA"
TimeGMTAdd(2, 0) = "ANKARA"
TimeGMTAdd(3, 0) = "ATHENS"
TimeGMTAdd(4, 0) = "AUCKLAND"
TimeGMTAdd(5, 0) = "BAGHDAD"
TimeGMTAdd(6, 0) = "BANBURY"
TimeGMTAdd(7, 0) = "BANGALORE"
TimeGMTAdd(8, 0) = "BANGKOK"
TimeGMTAdd(9, 0) = "BANSKA BYSTRICA"
TimeGMTAdd(10, 0) = "BARCELONA"
TimeGMTAdd(11, 0) = "BASINGSTOKE"
TimeGMTAdd(12, 0) = "BEIJING"
TimeGMTAdd(13, 0) = "BELFAST"
TimeGMTAdd(14, 0) = "BERLIN"
TimeGMTAdd(15, 0) = "BERN"
TimeGMTAdd(16, 0) = "BHUBANESWAR"
TimeGMTAdd(17, 0) = "BIRMINGHAM-GB"
TimeGMTAdd(18, 0) = "BISHOPS STORTFORD"
TimeGMTAdd(19, 0) = "BONN"
TimeGMTAdd(20, 0) = "BRACKNELL"
TimeGMTAdd(21, 0) = "BRATISLAVA"
TimeGMTAdd(22, 0) = "BRISBANE"
TimeGMTAdd(23, 0) = "BUCHAREST"
TimeGMTAdd(24, 0) = "BUCURESTI"
TimeGMTAdd(25, 0) = "BUDAPEST"
TimeGMTAdd(26, 0) = "BYLEY"
TimeGMTAdd(27, 0) = "CAIRO"
TimeGMTAdd(28, 0) = "CAMBERLEY"
TimeGMTAdd(29, 0) = "CAMPANILLAS MALAGA"
TimeGMTAdd(30, 0) = "CANBERRA CITY-AU"
TimeGMTAdd(31, 0) = "CAPE TOWN"
TimeGMTAdd(32, 0) = "CARNAXIDE"
TimeGMTAdd(33, 0) = "CASABLANCA"
TimeGMTAdd(34, 0) = "CHATEAUDUN"
TimeGMTAdd(35, 0) = "CHATEAUFORT"
TimeGMTAdd(36, 0) = "CHENNAI"
TimeGMTAdd(37, 0) = "CHORLEY"
TimeGMTAdd(38, 0) = "COPENHAGEN"
TimeGMTAdd(39, 0) = "DANANG"
TimeGMTAdd(40, 0) = "DIMAPUR"
TimeGMTAdd(41, 0) = "DUBAI"
TimeGMTAdd(42, 0) = "DUBLIN"
TimeGMTAdd(43, 0) = "EDOGAWA KU"
TimeGMTAdd(44, 0) = "FRANKFURT"
TimeGMTAdd(45, 0) = "FRIEDRICHSHAFEN"
TimeGMTAdd(46, 0) = "FUSHAN"
TimeGMTAdd(47, 0) = "GALWAY"
TimeGMTAdd(48, 0) = "GLASGOW"
TimeGMTAdd(49, 0) = "GLOUCESTERSHIRE"
TimeGMTAdd(50, 0) = "GROSSOSTHEIM"
TimeGMTAdd(51, 0) = "GUANGZHOU"
TimeGMTAdd(52, 0) = "GURGAON"
TimeGMTAdd(53, 0) = "GUWAHATI"
TimeGMTAdd(54, 0) = "HANOI-VN"
TimeGMTAdd(55, 0) = "HARLOW"
TimeGMTAdd(56, 0) = "HARYANA"
TimeGMTAdd(57, 0) = "HELSINKI"
TimeGMTAdd(58, 0) = "HO CHI MINH"
TimeGMTAdd(59, 0) = "HOHOLA"
TimeGMTAdd(60, 0) = "HONG KONG"
TimeGMTAdd(61, 0) = "HOOFDDORP"
TimeGMTAdd(62, 0) = "HYDERABAD"
TimeGMTAdd(63, 0) = "IMMENSTAAD"
TimeGMTAdd(64, 0) = "ISLAMABAD"
TimeGMTAdd(65, 0) = "ISTANBUL"
TimeGMTAdd(66, 0) = "JAKARTA"
TimeGMTAdd(67, 0) = "JEDDAH"
TimeGMTAdd(68, 0) = "JERUSALEM"
TimeGMTAdd(69, 0) = "KARACHI"
TimeGMTAdd(70, 0) = "KATOWICE"
TimeGMTAdd(71, 0) = "KIEV"
TimeGMTAdd(72, 0) = "KOCHI"
TimeGMTAdd(73, 0) = "KOLKATA"
TimeGMTAdd(74, 0) = "KRAKOW"
TimeGMTAdd(75, 0) = "KUALA LUMPUR"
TimeGMTAdd(76, 0) = "KWAI CHUNG"
TimeGMTAdd(77, 0) = "LA CORUNA"
TimeGMTAdd(78, 0) = "LAHORE"
TimeGMTAdd(79, 0) = "LAS PALMAS DE GRAN CANARIA"
TimeGMTAdd(80, 0) = "LISBON"
TimeGMTAdd(81, 0) = "LONDON-GB"
TimeGMTAdd(82, 0) = "LUX"
TimeGMTAdd(83, 0) = "LYON"
TimeGMTAdd(84, 0) = "MADRID"
TimeGMTAdd(85, 0) = "MAIDENHEAD"
TimeGMTAdd(86, 0) = "MANCHESTER"
TimeGMTAdd(87, 0) = "MANILA"
TimeGMTAdd(88, 0) = "MARSA"
TimeGMTAdd(89, 0) = "MEJORADA DEL CAMPO"
TimeGMTAdd(90, 0) = "MELBOURNE"
TimeGMTAdd(91, 0) = "MERSEYSIDE"
TimeGMTAdd(92, 0) = "MILAN"
TimeGMTAdd(93, 0) = "MONKSTOWN"
TimeGMTAdd(94, 0) = "MONRO"
TimeGMTAdd(95, 0) = "MOSCOW"
TimeGMTAdd(96, 0) = "MRIEHEL"
TimeGMTAdd(97, 0) = "MUMBAI"
TimeGMTAdd(98, 0) = "MUNICH"
TimeGMTAdd(99, 0) = "NAPLES"
TimeGMTAdd(100, 0) = "NEU ISENBURG"
TimeGMTAdd(101, 0) = "NEW TECH PARK"
TimeGMTAdd(102, 0) = "NEWCASTLE UPON TYNE"
TimeGMTAdd(103, 0) = "NEWPORT"
TimeGMTAdd(104, 0) = "NEWTON ABBOT"
TimeGMTAdd(105, 0) = "NIZHNY NOVGOROD"
TimeGMTAdd(106, 0) = "OSLO"
TimeGMTAdd(107, 0) = "OXON"
TimeGMTAdd(108, 0) = "PADOVA"
TimeGMTAdd(109, 0) = "PANYU CITY"
TimeGMTAdd(110, 0) = "PARIS"
TimeGMTAdd(111, 0) = "PATNA"
TimeGMTAdd(112, 0) = "PENANG"
TimeGMTAdd(113, 0) = "PERTH"
TimeGMTAdd(114, 0) = "PRAHA 1-CZ"
TimeGMTAdd(115, 0) = "RANCHI"
TimeGMTAdd(116, 0) = "RATINGEN"
TimeGMTAdd(117, 0) = "RIYADH"
TimeGMTAdd(118, 0) = "ROME"
TimeGMTAdd(119, 0) = "SAINT QUENTIN"
TimeGMTAdd(120, 0) = "SANDTON"
TimeGMTAdd(121, 0) = "SANTA CRUZ DE TENERIFE"
TimeGMTAdd(122, 0) = "SEOUL"
TimeGMTAdd(123, 0) = "SEVILLE"
TimeGMTAdd(124, 0) = "SHALIMAR"
TimeGMTAdd(125, 0) = "SHANGHAI"
TimeGMTAdd(126, 0) = "SHANNON"
TimeGMTAdd(127, 0) = "SHENYANG"
TimeGMTAdd(128, 0) = "SHILLONG"
TimeGMTAdd(129, 0) = "SHUNDE"
TimeGMTAdd(130, 0) = "SINGAPORE"
TimeGMTAdd(131, 0) = "SMALLHEATH"
TimeGMTAdd(132, 0) = "ST.PETERSBURG-RU"
TimeGMTAdd(133, 0) = "STOCKHOLM"
TimeGMTAdd(134, 0) = "STUTTGART"
TimeGMTAdd(135, 0) = "SURREY-GB"
TimeGMTAdd(136, 0) = "SWINDON"
TimeGMTAdd(137, 0) = "SYDNEY"
TimeGMTAdd(138, 0) = "TAIPEI-TW"
TimeGMTAdd(139, 0) = "TEL AVIV"
TimeGMTAdd(140, 0) = "TOKYO"
TimeGMTAdd(141, 0) = "TUNIS"
TimeGMTAdd(142, 0) = "UK FIELD ENG"
TimeGMTAdd(143, 0) = "ULM"
TimeGMTAdd(144, 0) = "UNKNOWN"
TimeGMTAdd(145, 0) = "USTER"
TimeGMTAdd(146, 0) = "VALBONNE-FR"
TimeGMTAdd(147, 0) = "VICTORIA ISLAND LAGOS"
TimeGMTAdd(148, 0) = "VIENNA-AT"
TimeGMTAdd(149, 0) = "VORONEZH-RU"
TimeGMTAdd(150, 0) = "WAKEFIELD"
TimeGMTAdd(151, 0) = "WARRINGTON"
TimeGMTAdd(152, 0) = "WARSAW"
TimeGMTAdd(153, 0) = "WELLINGTON"
TimeGMTAdd(154, 0) = "WEYMOUTH"
TimeGMTAdd(155, 0) = "WHITEFIELD"
TimeGMTAdd(156, 0) = "WYTHENSHAWE"
TimeGMTAdd(157, 0) = "XIAN-CN"
TimeGMTAdd(158, 0) = "ZAVENTEM"


' What will it convert to?
For SearchTimeGMT = 0 To 2
If UserLocation = TimeGMTSub(SearchTimeGMT, 0) Then
If Now() >= DSTStart And Now() <= DSTEnd Then
correctDST = TimeAction -
TimeSerial(TimeGMTSub(SearchTimeGMT, 1), 0, 0)
Else
correctDST = TimeAction -
TimeSerial(TimeGMTSub(SearchTimeGMT, 2), 0, 0)
End If
End If

If UserLocation = TimeGMTAdd(SearchTimeGMT, 0) Then
If Now() > DSTStart And Now() <= DSTEnd Then
correctDST = TimeAction +
TimeSerial(TimeGMTAdd(SearchTimeGMT, 1), 0, 0)
Else
correctDST = TimeAction +
TimeSerial(TimeGMTAdd(SearchTimeGMT, 2), 0, 0)
End If
End If
Next
End Function

-----
As you can see, I have some typing to do... but I would still like to know
if there is a better way to do this. The code is obviously being used in a MS
Access DB, but will also be called by a queries to adjust the data being
outputed...

I'd like to thank all the super smart people who'll reply to this in advance.
Thank you!

Mike

 

[John Spencer]

Personally, I would construct a table to hold all the information and use
that to adjust the time.

TimeZoneTable
Location
AdjustMinutes
DaylightStart
DayLightEnd

Are you adjusting just the time or do you also need to adjust the date. If
something happens at 23:08 UTC On Jan 2 and you are in a plus 2 time zone
the time becomes
Jan 3 at 01:08.

If I remember correctly there are places that are not a whole number of
hours different so you may want to specify the adjustment in minutes.
And not all countries observe daylight savings time
And countries have different rules for the implementation of daylight
savings time.

Good luck on a non-trivial exercise.

I would search for (Google, Yahoo, Live Search)
-- Time Zone Adjustments VBA
or
-- Time Zone Adjustments Visual Basic
and see what others have already done.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

As you can see, I have some typing to do... but I would still like to know
if there is a better way to do this. The code is obviously being used in a MS
Access DB, but will also be called by a queries to adjust the data being
outputed...

Well, for starters, posting 500+ lines of code to a message is... umm... a bit
impolite.

For another, your huge array of locations is going to be a major pain to
maintain. I'd VERY strongly recommend creating a Table of timezones. I haven't
the time at the moment to google for one, but I suspect that the data can be
downloaded from the web; if you load this information into a table, your code
could simply open a recordset or use DLookUp on the table to find the time
offset for any location. If you're worried about performance, you could move
the recordset into an array on opening the database (but try directly it
first, you might be surprised).

John W. Vinson [MVP]

 

[Mike]

Yup, got down to Bangkok ... so I made the adjustment from hours to minutes
to input that into the TimeSerial() function.

I've been using the TimeSerial() function for a bit and doing a lot of date
manipulation, MS has made sure that if the scenario you describes happens
(and it does in my data quite often) that it will roll back the day as well
or roll it forward depending on where the even took place.

I'm sure I did it that way before... but I've been on a programming kick
lately, trying to make myself smrtr (LOL)

I fiddled around with in VS2k8 in both VB and C#, obviouly I had to use my
VB stuff, but I noticed that C# had a niftly little class called TimeZone...
I didn't have much time to read up on it... and I would have no idea on how
to impliment it in MS Access anyways...

Cool, peace out