I don't want a decimal to be allowed in the number.

[Rob]

I have a form field that is necessarily unbound. It has to be.
(Don't ask.)

At present, if the user types a number into it, he or she is free to
stick decimals into the input as well. For example, the user could
type "1.21" instead of "1" and the field will just accept that.

I don't want the field to accept that. I would like it such that if
the user attempts to write "1.21" or whatever, the program will react
by saying, in so many words, "this is disallowed. Either put integers
into the field, or scram." (or whatever Access does in such events).

I've tried every option seemingly at my disposal. I must not know or
understand how to use the Expression Builder to create a Validation
Rule for this. The expression builder puts "=Int(<<Number>>)" into
the Rule, and the program subsequently treats every entry for that
field as invalid.

I've tried formatting "General Number." Nothing.

Help. (And if you do help, can you reply with a real live solution
instead of just some general instructions. I've tried deriving from
the latter already, and have failed.)

I could write another InStr routine that intercepts the "." but it is
high time I learned about whatever native functionality Access offers,
if any, to deal with this miserable condition.

Thank you for any information that you can provide.

-Rob

[Albert Kallal]

You have quite a few choices as to how you want to accomplish this.

Simply strip out the decimal stuff in the after update event. Hence the
code would look as follows:

Private Sub Text1_AfterUpdate()

Me.Text1 = Int(Me.Text1)

End Sub

Thus, the above code would simply dump anything after the decimal point. I
kind of like this approach, since the user does not get any type of error
message etc. The decimal stuff would go poof....fast and simple. The user
will quickly find out that the decimal stuff just goes away.

The next approach would be to write a custom verification routine. This
routine could check for a decimal, and complain to the user, and then
force the user (actually the cursor) back into the field. I supposed this
method might be a bit better, as the user will now get a nasty error
message telling them not to do this. Access has an event built just for
the purpose of "verifying* data after you enter it. After you play around
with the above code, try removing it, and using the following code. This
code goes into the *before update* event. This event has a cancel
option...just what the doctor ordered...

Private Sub Text1_BeforeUpdate(Cancel As Integer)

If Me.Text1 <> Int(Me.Text1) Then
Beep
MsgBox "Numbers only..no decimals allowed", vbExclamation
Cancel = True
End If

End Sub

--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com

[James Minns]

"Rob" <bxmnc...@hotmail.com> wrote in message
news:9601e2ae.0109...@posting.google.com...

> I have a form field that is necessarily unbound. It has to be.
> (Don't ask.)
>
> At present, if the user types a number into it, he or she is free to
> stick decimals into the input as well. For example, the user could
> type "1.21" instead of "1" and the field will just accept that.
>
> I don't want the field to accept that. I would like it such that if
> the user attempts to write "1.21" or whatever, the program will react
> by saying, in so many words, "this is disallowed. Either put integers
> into the field, or scram." (or whatever Access does in such events).

This seems to work:
Set the Validation Rule property of the text box to
"Like #"
Set the Validation Text property to
"Integers only please"

James

[Rob]

Albert,

I'm trying to get away from the VBA for this particular kind of
problem because I don't want to reinvent any wheels that Microsoft may
have already provided in the inherent Access functionality. I'm
horrified at the thought of having done so in the past--not partly
because there's got to be cases where I've done so already that,
accordingly, I don't know about.

Otherwise I'd jump at the chance to use your BeforeUpdate suggestion.

-Rob

[Rob]

James,

Bullseye. Perfect. With the minor modification:

Like "#"

it works wonderfully. "Integers only please." I like the sound of
it. (And I've copied it verbatim for the message.)

Thanks again,
-Rob

[Albert Kallal]

That will not work if you allow numbers larger than one digit.

Thus, if you want to allow 22, then the verification routine will not
work.

--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com

"Rob" <bxmnc...@hotmail.com> wrote in message

news:9601e2ae.01090...@posting.google.com...

[Rob]

Albert,

Please accept my humblest apology. I have concluded that your
solution works best for this job, and I've incorporated your
BeforeUpdate event code verbatim into my program.

The:

Like "#"

that I had tried works only for one place holder, i.e., 0-9.

Your solution covers multiple placeholders. I would still be
interested in understanding whether MS offers a Data Property
solution. Doesn't appear that way.

Hat in hand, thank you.
-Rob

[Albert Kallal]

Gee, nothing like a apology is needed!

(but thank you anyway).

Your attempt to question and "find" a good approach to the problem was all
you were trying to do!. No big deal if my solution was not best one
offered. I am sure someone else could come up with another approach. Also,
the solution offered *does* work fine, if you wanted only one digit to be
entered....hence learning still occurred here....

I suspect, that there might be a simple mask, or some other approach that
works better.

By the way, we could have skipped the "code" example, and not used the
before update. One could simply enter the following expression as a
validation rule in the properties sheet.

int([text1]) = [text1]

and in the validation text box (that comes right after the rule option in
the sheet), you could enter
Integers Only

(no quotes are needed around the text).

There are always many solutions, the real art of access is finding the
right one. Heck, we now have worked on about 3 solutions...(the after
update solution, the before update solution, and now the valuation rule
solution).

All in all, this turned out to be pretty good thread....

[Gary Harris]

Like "#" does seem to allow only 1 digit.

If you do get bored of trying to use Access in-built bits and pieces (or
there doesn't seem to be a solution) and want to use code, the following in
the KeyPress event does the job nicely.
Also it warns them while inputting, not when trying to leave the textbox,
which is better from a user-friendly point of view.
You would also need to verify its an integer that's entered afterwards (it
may be too big and not ranging in value from -32,768 to 32,767)!
I don't worry about "re-inventing the wheel" by using code in Access
anymore. At least it avoids the glichey inconsistencies that seem to arise
from Access's in built features.

In key press event:
Private Sub Text0_KeyPress(KeyAscii As Integer)
' This event cancels any keystroke other than 0-9, tab & backspace
If (KeyAscii < 48 Or KeyAscii > 57) And (KeyAscii <> 8) And (KeyAscii <>
9) Then
KeyAscii = 0 'Ignore keystroke
MsgBox "Integers only please"
End If
End Sub

to validate in the before update:
Private Sub Text0_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim intX As Integer
intX = CInt(Text0)
ExitHere:
Exit Sub
ErrorHandler:
If Err.Number = 6 Then 'Overflow error
MsgBox "Number too large!"
Cancel = True
ElseIf Err.Number = 94 Then 'Invalid use of null
MsgBox "You haven't entered a number!"
Cancel = True
Else
MsgBox Err.Description 'Shouldn't happen but hey!
End If
Resume ExitHere
End Sub

The above events are the standard way to validate user input (at least
according to the Microsoft recommended VB exam study guides!!) in a VB
application. I will be interested to hear if you get any further with
Access features though!

Gary

"Rob" <bxmnc...@hotmail.com> wrote in message

news:9601e2ae.01090...@posting.google.com...