Access Expression Builder

[hegaroo]

Im doin some work in Access and wanted to know what im doin wrong in the expression builder on a Products table.

I need the price field (type=currency), to have the following validation rule.
Price should be grater than zero and price cannot be a null entry.

Ive got the [Price] >0 but dont know how to tell it that Price should not be a null entry.

Help Appriciated
Thanks

[Spunkey]

Open the table in design view, and put the 'Required' value to yes for the field. Thats Access' way of disallowing null values - you shouldnt need to use validation rules to set it.

You could also include a default value to ensure none slip through in code.

[wolfdogg]

In Access, I have a form with a text box. The only validation rule that I need is that a value of 7 numbers can only be entered into it. No decimals or negative numbers or anything.. just a straight 7 positive digits, and no more or no less. I tried using the expression builder for the first time ever and inputting: [Case ID].SelLength = 7
(Case ID is my text box name)

It doesn't work though. Can someone help??
Thank you!
jk

[Spunkey]

Assuming the textbox is databound to a field in a table, make sure the field in the table is of 'number' type, then when in design mode of the form, set the validation rule as..

Like "???????"

This means that the entry MUST be 7 characters, and because the field is numeric, it will only allow valid numbers.

If the textbox is not databound, you'll need to put some code in to validate it.
On the Events tab of the properties window for the textbox, select 'Event Procedure' and then click the '...' button.
In the code window which appears, enter this in the Sub...

  If not isNumeric(TextboxName.value) then
    ' not a valid number
    msgbox("Enter a number jackass")
    Exit Sub
  Else
    ' it is a valid number
    If TextboxName.value <= 0 then
      ' its a negative number
      msgbox("Enter a positive number jackass")
      Exit Sub
    Else
      ' its a number, and its not negative
      If len(TextboxName.value & "") < 7 then
        ' Its not 7 characters
        msgbox("Enter a 7 digit number jackass")
        Exit Sub
      Else
        ' its a number, its not negative and its 7 characters - SUCCESS!
        ' Its valid, carry on processing your form, shove it in 
        ' the database, whatever you wanna do
      End If
    End If
  End If

OK thats got more nested IF's than you can shake a big pointy stick at, but it should work

[wolfdogg]

Excellent. That works. I also found I could input 0000000 in the Input Mask, but that was causing the cursor to jump to the end and I couldn't enter the numbers without highlighting the field. Your way works much better. The text box was bound to a table field.. so your short approach worked great! Thanks!!

As a follow up, I can now enter a new record and save it without a problem, but I don't like that you don't get any kind of prompt that the record is saved. Is there an ez way to accomplish this?

Thanks again! The part I really need answered now works well!
jk

[Spunkey]

glad it worked matey

I've never done the message after an update thing, but i can see why you want it. Access forms don't update a record until you move off it - as the tables do. I've never tried it, but give the below a go.

If you click on the area outside your form in design view, and go to the Events window in properties. Now, if you just want to popup a message after an insert, change the 'After Insert' to Event Procedure and click the dots. In the Sub which appears enter...

msgbox("Record has been successfully inserted")

To do the same for an Update to a record, you'll need to set the 'After Update' event on the props window.

Can't guarantee it'll work, but it's worth a shot