system to prevent field from being populated with number outside of range

[Bob Fitz]

Nope Bob we are are both wrong....

Well at least you've got it right now

[sheckay]

Hmmmm...same deal. It gives me the message no matter what number I enter. Do you think the issue is that the fields that are the set limits Me.DTPlus and Me.DTMinus are calculated fields? They are functioning, and displaying the correct value. But they are set to hold the result of the diameter plus the tolerance, and the other one is the diameter minus the tolerance.

[sheckay]

I've tried the field's validation rule too. > [DTMinus] And < [DTPlus] I also tried Or instead of And. I get error messages no matter what number I enter.

[Welshgasman]

Walk through the code and inspect the controls within the code.

[kd2017]

Why not use table validation?

Table validation rule property:

([lower_bound]<[upper_bound]) And ([my_number]>=[lower_bound]) And ([my_number]<=[upper_bound])

Table validation text property:

x has to be between y and z. Try again.

[kd2017]

Hmmmm...same deal. It gives me the message no matter what number I enter. Do you think the issue is that the fields that are the set limits Me.DTPlus and Me.DTMinus are calculated fields? They are functioning, and displaying the correct value. But they are set to hold the result of the diameter plus the tolerance, and the other one is the diameter minus the tolerance.

In light of this I made some adjustments to my previous suggestion

[Micron]

Hello Micron. Text94 is just the name of the text box used to enter a number.

I said 94, as in the value 94 - not Text94.

[sheckay]

Hmmm, I'll try that. I had read that to reference other fields, I'd have to use the field's validation rule. But I'll try the form's.

[kd2017]

Hmmm, I'll try that. I had read that to reference other fields, I'd have to use the field's validation rule. But I'll try the form's.

If this is directed at me note that I was talking about the *TABLE* validation properties. The table would validate itself no matter what form you used to input the data.

Side note: this should be a really straight forward task, either at table or form level. If you're still having problems at this point I suggest posting a copy of your db for review.

Another side note: you mentioned 'calculated fields'. Actual calculated fields within a table are baaad juju. But given the context of this thread I'm guessing they're calculated *controls*? Another reason it might be a good idea to post your db for review.

[sheckay]

Sorry. I must have misunderstood.

[keviny04]

I'm surprised this hasn't been solved yet after a week. And it's quite a simple problem. The comparison didn't work because it compared a STRING to a calculated value. To make it work, make sure all values are numeric. The calculated fields are numeric already. So you have to make sure the user entry is numeric too. Use the Val() function as follows:

Private Sub Text94_BeforeUpdate(Cancel As Integer)
    If Val(Me.Text94) < Me.DTMinus Then
    ' Display a message
           MsgBox "Your Entry is Outside of the Tolerance Range. Too low!!", vbExclamation
          ' Cancel the update
          Cancel = True
     End If
     If Val(Me.Text94) > Me.DTPlus Then
     ' Display a message
           MsgBox "Your Entry is Outside of the Tolerance Range. Too high!!", vbExclamation
           ' Cancel the update
          Cancel = True
     End If
End Sub

[Micron]

I asked for sample data back in post 7. No wonder a simple problem hasn't been solved 19 posts later.
Val stops reading numbers as soon as a character isn't a number, so if data/range is like P456 Val is of no use. I looked for sample data/ranges before deciding to say that and didn't see where any was offered so I think it's premature to suggest Val or anything else.

[keviny04]

I asked for sample data back in post 7. No wonder a simple problem hasn't been solved 19 posts later.
Val stops reading numbers as soon as a character isn't a number, so if data/range is like P456 Val is of no use. I looked for sample data/ranges before deciding to say that and didn't see where any was offered so I think it's premature to suggest Val or anything else.

Sometimes you need some sixth sense to solve problems like this. I didn't see any sample data either. I just tried all possibilities I could think of, as every troubleshooter should. Val() is just one way, and there are other ways. The crux of the matter is the user entry has to be converted to a numeric value in this case.