How to Trim values in a column with If Conditions

[taimysho0]

hello i have a column that is used for phone numbers. However, the values in this column are not all uniform. Some are, 10 digit (which is what i want) and some are more or less.



a lot of the values have a "9," and then the 10 digit number after. How do i write a function in the query so that if there is a "9," then trim it so that it only leavs the 10 digit numbers after it? thanks!

ex:

instead of 9,1234567891
it should show 1234567891

however, this should not be justified for the whole column, only some values have the "9,"

[taimysho0]

also, what should i put in the criteria to not include any values with less than 10 digits? thanks

[Bob Fitz]

Is the unwanted "9" always followed by a commer?

[taimysho0]

yes, it is

[Missinglinq]

You can't simply strip the '9,' off in the Control's BeforeUpdate event, which would seem like the obvious way to do this, because Access won't allow you to change the Value in this event, so I'd use the Form_BeforeUpdate event. See if this works for you:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Left(Me.TargetField, 2) = "9," Then
   Me.TargetField = Mid(Me.TargetField, 3)
   
   If Len(Me.TargetField) < 10 Then
    MsgBox "Telephone Number is Too Short!'"
    Cancel = True
    TargetField.SetFocus
   End If
 
 Else
   
   If Len(Me.TargetField) < 10 Then
    MsgBox "Telephone Number is Too Short!'"
    Cancel = True
    TargetField.SetFocus
   End If
 
 End If

End Sub

(Sorry, site won't let me edit my previous post)

Linq ;0)>

[taimysho0]

thanks linq, but what if im not using any forms? i only have queries and tables..

[Missinglinq]

End users should never, never, never have direct access to Tables! Never!

And if you're going to do the kind of thing you're asking about here, checking input and making corrections to that input, you're going to have to use Forms. You can use the Form Wizard to create your Forms and if all you want are Datasheet View Forms, i.e. those that look identical to Tables/Queries, they should take you all of a minute apiece to create.

Linq ;0)>

[taimysho0]

thanks linq, however im confused on how the code above will work on a form, as the user will not be inputting any phone numbers. the list of phone numbers is pulled from an odbc connection to a system program. im simply just pulling the phone numbers from that system table and cleaning it up and storing it in a table to be able to run future queries against it.. let me know if that makes sense

[Bob Fitz]

Try:
SELECT TableName.TelID, IIf(Left([TelNum],2)="9,",Mid([TelNum],3),[ TelNum]) AS Num
FROM TableName
WHERE ((Len(IIf(Left([TelNum],2)="9,",Mid([TelNum],3),[ TelNum]))=10));

You will need to change TableName your table name and change TelNum your field name.

[taimysho0]

thanks! that worked, last question is what if i want to trim everything to the right after 10 digits? for exampel if i have 1234567891,,,,, and i want to just have 1234567891?

something like (left([Numbcolumn]), 10))? but only for ones that have more than 10 digits after the after accounting for all the digits to the left

[taimysho0]

nevermind, i just the above and it worked