Results 1 to 5 of 5
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    two conditions on a form control

    Hi,



    In access leading zero's are omitted, so 025 will be 25 if the field data type is a number. To keep leading zero's the field data type is text; in this case 025 will be 025.

    I have a function fnValidation that checks to have only one dot ( . ) in the field. so 23.6 is accepted but 23..6 or 23.6.7 are not accepted as data entries.

    I also needs to check that no letters or other characters are included. so values such as 34h or 34# or (34 % d5) are not accepted as data entries.
    In other words: data for this text box should compose only of digits from 0-9 and one dot (.)

    How would my BeforeUpdate event for that text box be updated?

    The validation fnValidation has the following code:
    Code:
    Private Function fnValidation(txt As String) As Boolean
        
        If InStr(1, txt, ".") > 0 And InStr(InStr(1, txt, ".") + 1, txt, ".") > 0 Then
            MsgBox "Only one full stop (.) permitted", vbCritical, "Invalid Entry..."
            fnValidation = False
            Exit Function
             Else
            fnValidation = True
        End If
        
    End Function


    The BeforeUpdate events is:
    Code:
    On Error GoTo ProcError
    
        If fnValidation(Me.txtCallNumber) = False Then
        cancel = True
    End If
    Sincerely yours
    Khalil

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Just use a format on the control to show leading zeroes?

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First things first...and the first question is...why define the field as a number? If users are apt to enter 23..6 or 23.6.7...or alpha characters...it doesn't sound as if it's actually a numeric field...but simply a field of digits. Fields containing digits, but never used in math, should be defined as Text...not Numbers.

    Second question...why use a Function? Does this validation need to be done in multiple places, in you app?

    A plain language explanation of your process, here, would help us to help you.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    The field is defined as text and not number for two reasons:
    1- It is not used in math.
    2- To show leading zero in some of the data.

    The function is used in two places in the database.
    In brief, data contains only digits with one dot ( . ) only when needed. Also data might have leading zero.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You would need to do 2 things in the event
    - loop over the string using Instr function and add each occurrence of a character to a variable. If the count is greater than desired, raise the message.
    - in that looping, might as well test if the character being examined falls between the ASCII character code numbers for 0 to 9 and raise a message if not. Otherwise, this would be another loop block of code, but I don't see the sense in going over it twice when you could do it once.

    You would have to make an exception (IF statement) for when the character code returned is a period/dot. This all assumes that a standard English character set is involved if you were to test alpha characters instead. Since you posted that you only want numbers the standard code values for numbers should work.
    Last edited by Micron; 09-19-2020 at 11:54 AM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-18-2016, 12:06 PM
  2. Replies: 1
    Last Post: 04-30-2014, 05:26 PM
  3. Prevent form updates for certain conditions.
    By gg80 in forum Programming
    Replies: 5
    Last Post: 03-29-2014, 09:21 PM
  4. Conditions In Form
    By drunkenneo in forum Forms
    Replies: 1
    Last Post: 06-21-2013, 02:15 PM
  5. Replies: 1
    Last Post: 09-19-2012, 12:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums