#Error thrown from `Switch` when string parsing in Access

Question

I'm trying to do SWITCH statement in Access to parse GPS latitudes and longitudes of multiple formats into Decimal Degrees format

SELECT
  IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", ""))) as _spaces,
  Switch(
    ISNull([_spaces]), A.Lat,
    [_spaces] = 0, CDbl(A.Lat), 
    [_spaces] = 1, CDbl(Left(A.Lat,2)) + CDbl(MID(A.Lat,4,6))/60,
    [_spaces] = 2, CDbl(Left(A.Lat,2)) + CDbl(MID(A.Lat,4,2))/60 + CDbl(RIGHT(A.Lat,LEN(A.Lat)-6))/3600,
    1=1, CDbl(A.Lat)
  ) as [Lat]
FROM AggregateTags as A

but that returns #Error for all except where _spaces=0.

So, I thought I'd debug by simplify the conditions, by only trying to parse where _spaces is 1 or 2. Further, in case my parsing was wrong where _spaces=2 I thought I'd just put an arbitrary value for that condition. I also re-named the output result column, just in case there was any circularity in the references.

SELECT 
 IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", ""))) as _spaces,
 Switch(
    [_spaces]=2, 50.0,
    [_spaces]=1, CDbl(Left(A.Lat,2)) + CDbl(MID(A.Lat,4,6))/60
  ) as Latt
FROM AggregateTags as A

This works for _spaces=1, but still throws an #Error where _spaces=2.

I tried CDbl casting 50.0 or 50. I also tried to see if there was some issue with evaluation order by eliminating the reference to a column declaration from earlier in the query: SWITCH(IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", "")))=2, 50.0, ...) but that still throws the #Error where _spaces=2. The same occurs when swapping the placement of the various internal condition + result pairings.

Oddly, I do not get the error when I leave off the [_spaces]=1 and have simply the [_spaces]=2 and a default condition

SELECT 
 IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", ""))) as _spaces,
 Switch(
    [_spaces]=2, CDbl(50),
    1=1, A.Lat
) as Latt
FROM AggregateTags as A

Why is this throwing #Error for [_spaces]=2 when [_spaces]=1 is included also?

Sample data for the lat column

39 14.838
39 27 15.88
39.20628
"" (empty string)
NULL

Answer 1

The Switch statement in MS Access is prototyped as Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )

Access evaluates ALL values in the Switch statement, and if an error occurs in any - even if the expr does not match - it gives an #Error as in the following demonstration.

SELECT
  testing.ID as nbr,
  CDbl(LEFT(nbr,3)) as L,
  CDbl(RIGHT(nbr,3)) as R,
  SWITCH(1=1,999,0=1,CDbl(0)) as intval,
  CDbl(RIGHT(nbr,5)) as errVal,
  SWITCH(1=1,999,0=1,CDbl(RIGHT(nbr,5))) as first
FROM testing

Where the only value in the short text ID column is "123 456", this yields a result of

  --------------------------------------------------
  |   nbr   |  L  |  R  | intval | errVal |  first |
  | ---------------------------------------------- |
  | 123 456 | 123 | 456 |    999 | #Error | #Error |
  --------------------------------------------------

Obviously 1=1 is always true and 0=1 is always false. Despite that, Access still evaluates both "values."

File another entry under "Access is dumb"

---

As to my parser, all my coordinates are in North America west of the Rockies, so all lats are 2 digits before the decimal pt and all longs are negative 3 digits. A more robust parser would find the positions of each of the spaces and break up the numbers accordingly, and may also look for and eliminate symbols (°, ' and ").

Due to the craziness that is the evaluation method of SWITCH with respect to error generation, I switched to IIF.

SELECT 
  IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", ""))) AS _Tspaces,
  IIF(ISNull(a.Lat), NULL, Len(A.lat) - Len(Replace(A.lat," ", ""))) AS _Gspaces,
  IIF(
    ISNull([_Tspaces]), NULL, IIF(
      [_Tspaces]=1, CDbl(Left(A.Lat,2)) + CDbl(RIGHT(A.Lat,6))/60, IIF(
        [_Tspaces]=2, CDbl(Left(A.Lat,2)) + CDbl(MID(A.Lat,4,2))/60 + CDbl(RIGHT(A.Lat,LEN(A.Lat)-6))/3600, IIF(
          [_Tspaces]=0, CDbl(A.Lat), A.Lat
        )
      )
    )
  ) AS Latitude,
  -1*IIF(
    ISNull([_Gspaces]), NULL, IIF(
      [_Gspaces]=1, ABS(CDbl(Left(A.Long,4))) + CDbl(RIGHT(A.Long,7))/60, IIF(
        [_Gspaces]=2, ABS(CDbl(Left(A.Long,2))) + CDbl(MID(A.Long,5,2))/60 + CDbl(RIGHT(A.Long,LEN(A.Long)-7))/3600, IIF(
          [_Gspaces]=0, ABS(CDbl(A.Long)), A.Long
        )
      )
    )
  ) AS Longitude
FROM AggregateTags AS A;