Validation rule in access

[ckpoll2]

Hello,

I have a validation rule in a text box on a form. The rule is that if Combo12 starts with "Credit Hours -*" than only allow numbers less than or equal to 2 in there. Any number of words could come after credit hours, hence the wildcard character. The formula that I'm using is:

[Hours]<=2 Or [Combo12]<>"Credit Hours -*"

but it isn't working. What am I doing wrong?

Thanks,

Charlie

[racquetballguy]

How about something like

Val(Mid(thestring,start position of #,Lenght of #))

If you need to get total length of string for Length of #, you can use Len

Ivan

[ckpoll2]

That didn't seem to work. Do you know of any variation of the formula that I listed in my earlier thread? I'm thinking it has something to do with using "like", but I'm not really sure.

Thanks for your help

[racquetballguy]

Are you comparing what is entered to 2 seperate parameters "hours" and "combo 12"? Does your user have to enter the text "credit hours - "?

If all they are really entering is a number, just compare it locally for that specific number.

If this is coming from a table that has the text "credit hours - " already there you are better off using a query.

Ivan

[ADezii]

Hello,

I have a validation rule in a text box on a form. The rule is that if Combo12 starts with "Credit Hours -*" than only allow numbers less than or equal to 2 in there. Any number of words could come after credit hours, hence the wildcard character. The formula that I'm using is:

[Hours]<=2 Or [Combo12]<>"Credit Hours -*"

but it isn't working. What am I doing wrong?

Thanks,

Charlie

'Assuming the Text Box is called txtTest, try this in the BeforeUpdate()
'Event of this Text Box to enforce Validation

Private Sub txtTest_BeforeUpdate(Cancel As Integer)
If Me![Combo12].Value Like "Credit Hours -*" Then
  If Me![txtTest].Value <= 2 Then
  Else
    MsgBox "Value must be <= 2"
    Cancel = True
  End If
End If
End Sub

[NeoPa]

Hello,

I have a validation rule in a text box on a form. The rule is that if Combo12 starts with "Credit Hours -*" than only allow numbers less than or equal to 2 in there. Any number of words could come after credit hours, hence the wildcard character. The formula that I'm using is:

[Hours]<=2 Or [Combo12]<>"Credit Hours -*"

but it isn't working. What am I doing wrong?

Thanks,

Charlie

Try something like

[Hours]<=2 Or [Combo12] Not Like "Credit Hours -*"

[MMcCarthy]

Logically, I don't think this rule will work.

You are saying that the the rule is that the hours have to be less than 2 or the Combo12 not like "Credit Hours -*". I don't think you can do this kind of logic with a validation rule and would suggest you look at Adezii's solution.

Mary

[NeoPa]

You're right Mary.
Rather than VBA code (which would work) you could try :

<=IIf([Combo12] Like "Credit Hours -*",2,99999)

[MMcCarthy]

You're right Mary.

Of course I'm right, I'm always right. :D

[NeoPa]

Of course I'm right, I'm always right. :D

Come now Mary, you should be able to remember more than half of your posts :wicked grin:

[MMcCarthy]

Come now Mary, you should be able to remember more than half of your posts :wicked grin:

Half, HALF !!!!!!!!

[NeoPa]

Come now Mary, you should be able to remember more than half of your posts :wicked grin:

Half, HALF !!!!!!!!

Maybe that was a little churlish - Five eighths maybe.

[MMcCarthy]

Maybe that was a little churlish - Five eighths maybe.

I refuse to disintegrate this argument into it's normal childish level. I am above such juvenile behaviour.

[NeoPa]

I refuse to disintegrate this argument into it's normal childish level. I am above such juvenile behaviour.

Lol Yeah, and what about the Leprechauns? Are they real too?

[MMcCarthy]

Lol Yeah, and what about the Leprechauns? Are they real too?

Loathe as I am to repeat myself ....

GET LOST ADE :D