Help Needed with a Tricky Validation Issue

[AB]

I have a text field in a database that the only formats that can be entered
are as follows:

v99.9 , 999.9 v99, v99.99, 999, 999.99

the 9’s can be any number

ex: v34.6 or 234.5 or v45 or v34.55 etc
what is not acceptable is 79.90

How can I check the data being input to make sure it is in one of the
allowable formats?

The data is entered in a textbox on a form in an Access 2007 database

Thanks!

AB

 

[Jack Leach]

So you want to make sure that, if there is only two numbers to the left of
the decimal, the value is prefixed with a v? I have to wonder what this "v"
stands for, and why you are trying to store it (the presence of a supposed
requirement for a prefix indicates that this field has some sort of attribute
tied to it, which should be handled in a seperate field, not this way...)

In any case, a function like this might work:

Public Function ValidateVNumber(sValue As String) As Boolean
Dim Ret As Boolean
Dim iPos As Integer
'if the length of the string is two and it's a number then fail
If Len(sValue) = 2 Then
If Isnumeric(sValue) Then
Goto Exit_Proc
End If
End If

'if there's a decimal and the first two chars are numbers then fail
iPos = Instr(1, sValue, ".")
If iPos = 3 Then
If Isnumeric(Left(sValue, 2)) Then
Goto Exit_Proc
End If
End If

Ret = True
Exit_Proc:
ValidateVNumber = Ret
Exit Sub
End Sub


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jeff Boyce]

I'm with Jack ... forcing a numeric value to have a "v" in its field seems
.... suspect.

Would it work to use two fields, one of which holds a numeric value (a true
number), and the other one gets a "v" as needed?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dirk Goldgar]

So you want to make sure that, if there is only two numbers to the left of
the decimal, the value is prefixed with a v? I have to wonder what this
"v"
stands for, and why you are trying to store it

They sound like ICD9 diagnosis codes to me.

 

[John Spencer]

One minor modification, since zero is not acceptable in the last position
after a period.

Like "v##.[1-9]"
Or Like "###.[1-9]"
Or Like "v##"
Or Like "v##.#[1-9]"
Or Like "###"
Or Like "###.#[1-9]"

That gets more complex if

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Actually, the phrase should have been deleted. I noticed that the expression
would work. Although, I might have also added a test to allow nulls or
zero-length strings if the field was not required.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

Yes, if zero is not allowed. I did not get that the last digit could
not be a zero. AB stated the 9s can be any number.

You wrote: "That gets more complicated if" but did not finish. I would
be interested to know your throughts.

Clifford Bass

One minor modification, since zero is not acceptable in the last position
after a period.

Like "v##.[1-9]"
Or Like "###.[1-9]"
Or Like "v##"
Or Like "v##.#[1-9]"
Or Like "###"
Or Like "###.#[1-9]"

That gets more complex if

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County