title: Access update query unable to update specific records
body: <p>I have a table with a column of ping-able computer names that is given to me as part of a larger table.  The computer names may contain a dotted domain name and/or be an IP address.  I need to separate out the computer name and domain name into their own columns.</p>
  
  <p>For example:</p>
  
  <pre><code>ComputerFullName   | ComputerName | Domain
  comp1              |              |
  1.2.0.1            |              |
  comp3.place.com    |              |
  1.2.1.45.place.com |              |
  </code></pre>
  
  <p>I can use the following query to fill in Domain:</p>
  
  <pre><code>UPDATE Example 
  SET Domain = SWITCH(
     ComputerFullName LIKE '#*.#*.#*.#*.*', MID(ComputerFullName, INSTR(1, REPLACE(ComputerFullName, '.', ' ', 1, 3), '.') + 1)
     , ComputerFullName LIKE '#*.#*.#*.#*', NULL
     , INSTR(1, ComputerFullName, '.') &lt;&gt; 0, MID(ComputerFullName, INSTR(1, ComputerFullName, '.') + 1)
  );
  </code></pre>
  
  <p>I've tried several queries to update the ComputerName column, the most promising was:</p>
  
  <pre><code>UPDATE Example 
  SET ComputerName = SWITCH(
     ComputerFullName LIKE '#*.#*.#*.#*.*', LEFT(ComputerFullName, INSTR(1, ComputerFullName, Domain) - 2)
     , ComputerFullName LIKE '#*.#*.#*.#*', ComputerFullName
     , INSTR(1, ComputerFullName, '.') &lt;&gt; 0, LEFT(ComputerFullName, INSTR(1, ComputerFullName, '.') - 1)
     , TRUE, ComputerFullName
  );
  </code></pre>
  
  <p>This and every other attempt has returned an error saying "Microsoft Office Access can't update all the records in the update query...Access didn't update 2 field(s) due to a type conversion failure..."</p>
  
  <p>The resulting table looks like: </p>
  
  <pre><code>ComputerFullName   | ComputerName | Domain
  comp1              |              |
  1.2.0.1            |              |
  comp3.place.com    | comp3        | place.com
  1.2.1.45.place.com | 1.2.1.45     | place.com
  </code></pre>
  
  <p>The table I want is:</p>
  
  <pre><code>ComputerFullName   | ComputerName | Domain
  comp1              | comp1        |
  1.2.0.1            | 1.2.0.1      |
  comp3.place.com    | comp3        | place.com
  1.2.1.45.place.com | 1.2.1.45     | place.com
  </code></pre>
  
  <p>Any suggestions?</p>
  
  <hr>
  
  <p>While working with the below answer I realized why my above query doesn't work.  Access evaluates each possible value in the SWITCH statement even if the condition is false.  Because of this, the length parameter of the LEFT functions were negative numbers when there was no domain.</p>