CODEWITHSUNDEEP
sqlms-access
user3250548
user3250548

Reputation: 13

Inserting part of a value from one field into another

I'm working within one table of a MS Access DB. I would like to use an iif statement to determine if a value from Field A conforms to a valid format (in this case, either one or two numbers followed by a letter). If it does, I would like to take just the numeric portion of Field A (e.g., if an entry for Field A is "15B", I would like to consider only the "15" part) and insert it into a currently empty Field B that I have created.

How can I write a MS Access query that only considers the numeric portion of Field A and then inserts it into Field B?

Upvotes: 1

Views: 101

Answers (3)

HansUp
HansUp

Reputation: 97101

For the validation part of your question, you can use Like pattern-matching. Here is an example from the Immediate window.

? "15A" Like "#[A-Z]" Or "15A" Like "##[A-Z]"
True

? "4B" Like "#[A-Z]" Or "4B" Like "##[A-Z]"
True

? "123A" Like "##[A-Z]" Or "123A" Like "##[A-Z]"
False

? "15AB" Like "#[A-Z]" Or "15AB" Like "##[A-Z]"
False

? "15!" Like "#[A-Z]" Or "15!" Like "##[A-Z]"
False

If those tests correctly express your intent, you could use this as the Validation Rule for Field A:

Like "#[A-Z]" Or Like "##[A-Z]"

As for Field B, you could make that a field expression in a query.

SELECT
    [Field A],
    Val([Field A]) AS [Field B]
FROM YourTable;

Use that query anywhere you need to see [Field B]. With that approach, if [Field B] doesn't exist in your table, you needn't be concerned about updating [Field B] stored values whenever [Field A] values change.

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6856

For the pattern you described, you can build an update query with like, like this: 

UPDATE tbl1 SET tbl1.ValB = 
    Switch([ValA] Like "#[a-z]",Left([valA],1),
    [valA] Like "##[a-z]",Left([valA],2),True,NULL);

Or use the Val function, which will try to convert as much as possible from the string into a number: 

UPDATE tbl1 SET tbl1.ValB = 
iif(valA like "#[a-z]" or valA like "##[a-z]",val(ValA),NULL)

Upvotes: 1

IgorM
IgorM

Reputation: 1356

You will need id field as well

Private Sub bntStart_Click()
    On Error GoTo err_trap

        Dim con As ADODB.Connection
        Dim rsLocal, rsLocal_Upd As ADODB.Recordset

        Dim sql_string As String
        Dim counter, numeric_value As Integer

        Set counter = 1

        sql_string = "SELECT * FROM TblTest"

        Set con = CurrentProject.Connection
        Set rsLocal = con.Execute(sql_string)

        If Not (rsLocal.BOF And rsLocal.EOF) Then
            rsLocal.MoveFirst
            While Not rsLocal.EOF
                If Len(ExtractNumber(rsLocal.Fields("FieldA")) > 0) Then
                    numeric_value = ExtractNumber(rsLocal.Fields("FieldA"))
                    sql_string = "UPDATE TblTerms SET FieldB = " & numeric_value & " WHERE id = " & counter
                    Set con = CurrentProject.Connection
                    Set rsLocal_Upd = con.Execute(sql_string)
                End If
                rsLocal.MoveNext
                counter = counter + 1
            Wend
        End If

        Set con = Nothing
        Exit Sub

    err_trap:
        MsgBox (Err.Description)
        Set con = Nothing

    End Sub

    Function ExtractNumber(str As Srting)
        Dim iCount As Integer, i As Integer
        Dim sText As String
        Dim lNum As String


        'Written by OzGrid Business Applications
        '[url]www.ozgrid.com[/url]

        'Extracts a number from a cell containing text And numbers.

        sText = str

        For iCount = Len(sText) To 1 Step -1
            If IsNumeric(Mid(sText, iCount, 1)) Then
                i = i + 1
                lNum = Mid(sText, iCount, 1) & lNum
            End If

            If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
        Next iCount

        ExtractNumber = CLng(lNum)
    End Function

Please be advised that I took the ExtractNumber from here http://www.ozgrid.com/forum/showthread.php?t=18185

Upvotes: 1

Related Questions