Access update query unable to update specific records

Question

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.

For example:

ComputerFullName   | ComputerName | Domain
comp1              |              |
1.2.0.1            |              |
comp3.place.com    |              |
1.2.1.45.place.com |              |

I can use the following query to fill in Domain:

UPDATE Example 
SET Domain = SWITCH(
   ComputerFullName LIKE '#*.#*.#*.#*.*', MID(ComputerFullName, INSTR(1, REPLACE(ComputerFullName, '.', ' ', 1, 3), '.') + 1)
   , ComputerFullName LIKE '#*.#*.#*.#*', NULL
   , INSTR(1, ComputerFullName, '.') <> 0, MID(ComputerFullName, INSTR(1, ComputerFullName, '.') + 1)
);

I've tried several queries to update the ComputerName column, the most promising was:

UPDATE Example 
SET ComputerName = SWITCH(
   ComputerFullName LIKE '#*.#*.#*.#*.*', LEFT(ComputerFullName, INSTR(1, ComputerFullName, Domain) - 2)
   , ComputerFullName LIKE '#*.#*.#*.#*', ComputerFullName
   , INSTR(1, ComputerFullName, '.') <> 0, LEFT(ComputerFullName, INSTR(1, ComputerFullName, '.') - 1)
   , TRUE, ComputerFullName
);

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..."

The resulting table looks like:

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

The table I want is:

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

Any suggestions?

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.

I'll clarify the question. comp1 and 1.2.0.1 should be in the ComputerName column. — mjoshawa, Nov 27 '12 at 21:13
This is occurring locally in Access. I could use a function for this (such as iterating through a recordset), but from my experience an Access SQL query is much faster. There could potentially be 10s of thousands of records. — mjoshawa, Nov 27 '12 at 21:40

score 2 · Accepted Answer · answered Nov 28 '12 at 04:04

I think what @HansUp was referring to was calling a function from within your query to split your names. Try this function:

Function SplitName(ByRef CFN As String, PartWanted As Integer) As String
    Dim CFN2 As String
    Dim I As Integer
    CFN2 = Replace(CFN, ".", "")
    If IsNumeric(CFN2) Then 'assume it's an IP address
        CFN = CFN & "|"
    Else
        Select Case Len(CFN) - Len(CFN2) 'we count the dots
            Case Is > 1 'at least 2 dots means computer & domain names
                I = InStrRev(CFN, ".")
                I = InStrRev(CFN, ".", I - 1)
                Mid(CFN, I) = "|"
            Case Is = 1 ' 1 dot means domain name only
                CFN = "|" & CFN
            Case Else '0 dots means computer name only
                CFN = CFN & "|"
        End Select
    End If
    SplitName = Split(CFN, "|")(PartWanted)
End Function

The PartWanted parameter would be either a 0 (to get the computer name) or 1 (to get the domain name). So your query would look like:

UPDATE Example 
SET Computername = SplitName([ComputerFullName],0), Domain = SplitName([ComputerFullName],1);

This runs pretty fast. I tested it and it took 13 seconds to call this function 2 million times (this didn't include the actual updating, just the calling).

I didn't know you could call a function from inside the query. I'll give this a try this afternoon. Thank you. — mjoshawa, Nov 28 '12 at 18:26
You can as long as you're running the query from within Access. It will not work if you're just hitting the DB through ODBC or some other link. — Tom Collins, Nov 28 '12 at 22:15
This worked as well as you said it would. Thank you very much. — mjoshawa, Nov 29 '12 at 18:42

Access update query unable to update specific records

1 Answers1