CODEWITHSUNDEEP
sqlms-access-2007jet-sql
mjoshawa
mjoshawa

Reputation: 115

Access update query unable to update specific records

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.

Upvotes: 0

Views: 484

Answers (1)

Tom Collins
Tom Collins

Reputation: 4069

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

Upvotes: 2

Related Questions