Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    11

    Question Unanswered: validation of text field - NO Numbers!

    Im trying to put some validation into a customer table. Ive got a mask working for the phone number field and postcode but i need to validate my address and name fields to ensure there are no numbers in them. Validation is only required at table level so i guess theres a handy little command i could use, can someone let me know!!(Its for a college assignment and my tutors said no VB experience needed).

    Cheers!

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Re: validation of text field - NO Numbers!

    Paul,
    If there are NO numbers for a given field then just set it's property to TEXT. Not too sure about validating an address field as there can be more than one address in more than one area. Only then it would vary according to region or something like that. How do you have your table setup? Could you post a snippet of it on here for us to see?

    Bud

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    11
    hmm not sure if you've got what im getting at.
    Im from the united kingdom and all of our phone numbers start with a 0 like my number is 01904 xxxxxx. If you store that as a number field it cuts off the 0 on the start so i've bin told by my tutor it should be saved as type text. so im storing a text field but i need to make sure that it only contains numbers!
    My other issue is with names and address fields. as you can see my address is normalised so theres a field for the number (of type number) and then several fields for the rest of the address. Each of these is type text.
    Im not sure if this is what would be done in the real world cos im a student but basicaly we have to make this idiot proof to ensure that there are no numbers in these fields.
    Attached Thumbnails Attached Thumbnails custtab.bmp  

  4. #4
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    Setting the field to text is the right thing to handle your leading zeros. But it does not handle the situation when there is meant to be no "text." Digits are legitimate text characters. (C3PO, R2D2, H2O, ...)

    Look up input masks in Help. Here's what I did:

    I put this into the input mask property of my field in the table definition:

    9999999999;;

    That allows entry of up to 10 optional digits in the field. It beeps when anything else is entered. They're handled as text, however, because my data type is set to text.

    A better solution would be to add code to validate during data entry because you can use error handling to give them meaningful error messages. But this is probably enough for your assignment.

    In the real world, we don't usually use denormalized street addresses unless we're doing really focused marketing or other really intense demographic analysis.

    Here in the US, I usually see people with a field for Street Address (number, street name as well as apartment or suite number), one for City, a third for State Abbreviation and a fourth for our ZipCode. For apps that may handle international addresses, we'd add Country and figure out how to handle Canadian Provinces or other European/South American Addressing standards.

    Magee
    Last edited by mageem; 02-10-04 at 21:05.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    11
    Excellent!! cheers Magee thats sorted my phone number problem!I have some basic forms set up and have found that if you set up a mask after creating an input field on a form it doesn't work.Does that sound like normal behaviour??
    So theres just the no numbers in text bit to sort now. I see what you mean with the number and street name fields made me think if i have house num as number what if i get someone living at 1a. Anyway i've just sent an email to my tutor pleeding with him to tell me how to do it! If he replies and it gets sorted ill post up the solution!!
    thanx again!

  6. #6
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    That doesn't sound like normal behavior. But in the last several versions Access has started doing "tricky" things supposedly in the interest of "helping" users. So I can't promise you that what you're seeing isn't meant to happen.

    I'd recreate the controls or just add the same input mask to the ones that already exist even though that should be redundant.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    11

    Solution!!

    To set up a mask to allow optional entry of only letters enter

    ???????????????????

    if a field is set to length 20 then enter 20 ?

    This allows between 0 and 20 characters to be entered but no numbers!!

  8. #8
    Join Date
    Dec 2003
    Posts
    268
    I think I know what you are looking at. If this is going to be controlled via a form you could set up a function to check this prior to saving

    Function allTextAreNumbers(str As String) As Boolean
    Dim i As Integer

    For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then
    allTextAreNumbers = True
    Else
    allTextAreNumbers = False
    Exit Function
    End If
    Next i
    End Function

    As for the normalization of your tables. I don't think there is a great deal of gained benefit for breaking it out that far. It is well beyond 3NF, which is genrally accepted as good paractice. However, you can modify the existing code to ensure that it is text is entered by switching the true and false values. If its isn't numeric then it will return true and verse vica.

    HTH (if I am understanding what you want to do correctly)

  9. #9
    Join Date
    Dec 2003
    Posts
    268
    Here is the code for text checking:

    Function allTextAreText(str As String) As Boolean
    Dim i As Integer

    For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then
    allTextAreText = False
    Exit Function
    Else
    allTextAreText = True

    End If
    Next i
    End Function

  10. #10
    Join Date
    Feb 2004
    Location
    UK
    Posts
    11

    cheers!

    This is just for an assignment in which i only have to validate at a basic level (ie in the tables). But thats a nice bit of code ill use that in my project!
    Thanx

  11. #11
    Join Date
    Oct 2013
    Posts
    1
    Here is the code allowing decimal places, if you need your code to have that.

    Public Function AllTextAreNumbers(str As String) As Boolean
    Dim i As Integer
    Dim DecimalPointCount As Integer
    DecimalPointCount = 0

    If IsNull(str) Then
    AllTextAreNumbers = False
    Exit Function
    End If

    str = LTrim$(str)

    For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then
    AllTextAreNumbers = True
    Else
    ' Allow decimal point
    If Mid(str, i, 1) <> "." Then
    AllTextAreNumbers = False
    Exit Function
    Else
    ' Allow one decimal point only
    DecimalPointCount = DecimalPointCount + 1
    If DecimalPointCount > 1 Then
    AllTextAreNumbers = False
    Exit Function
    End If
    End If
    End If
    Next i
    End Function

    Quote Originally Posted by mjweyland View Post
    I think I know what you are looking at. If this is going to be controlled via a form you could set up a function to check this prior to saving

    Function allTextAreNumbers(str As String) As Boolean
    Dim i As Integer

    For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then
    allTextAreNumbers = True
    Else
    allTextAreNumbers = False
    Exit Function
    End If
    Next i
    End Function

    As for the normalization of your tables. I don't think there is a great deal of gained benefit for breaking it out that far. It is well beyond 3NF, which is genrally accepted as good paractice. However, you can modify the existing code to ensure that it is text is entered by switching the true and false values. If its isn't numeric then it will return true and verse vica.

    HTH (if I am understanding what you want to do correctly)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •