Writing an Expression

[mikepf]

In the expression IIF([SSN]=50,99,[SSN]) when I enter 60, all works well. But if I enter 50, I get "The value entered doesn't meet the validation rule for the field or control". BTW SSN is defined iin a tabale as Number (long intiger). Any thoughts?

 

[genomon]

The validation rule for that field does not allow 99 as a valid SSN. Not surprising. Try changing or deleting the rule.

"Time flies like an arrow; fruit flies like a banana."

 

[mikepf]

But I put the expression in the validation rule. Where should the expression be placed?

 

[genomon]

OK, restart. Your expression is the validation rule.
Does this, from Access help, apply here?:

If you create a control by dragging a field from the field list, the field's validation rule remains in effect, although it isn't displayed in the control's ValidationRule property box in the property sheet. This is because a field's validation rule is inherited by a control bound to that field.

"Time flies like an arrow; fruit flies like a banana."

 

[mikepf]

So how do I edit the SSN field to change all 50 to 99? I almost feel that this edit should be put someplace else. If so where?

 

[genomon]

Assuming that "SSN" means Social Security Number, why are you storing two digit SSNs on your database? If you need a way to turn any occurence of 50 into 99 within the SSN then a better place to do this is on data entry, not in the table. There are all kinds of string functions you can investigate to accomplish this, such as InStr(), SubStr(), InStrRev(), and possibly the Format() function.

"Time flies like an arrow; fruit flies like a banana."

 

[mikepf]

Well, SSN in this case is not social security number. I was trying to use the Expression Builder for the field to change 50 to 99. Can you tell me the correct code to enter in Expression Builder to do this?

 

[genomon]

But I put the expression in the validation rule. Where should the expression be placed?

If this is in a text box on a form, try setting its ControlSource property to: =IIf([SSN]=50,99,[SSN])
including the = sign with no space between it and the IIf.

"Time flies like an arrow; fruit flies like a banana."

 

[genomon]

Still not clear on what you are doing; if you are trying to display data on a form, the above should work. If you're trying to change data on a table, first choice is to add editing to the data entry form. Second, to fix "bad" data, you could run an SQL update on the table that sets the field = 99 where it's = 50.

"Time flies like an arrow; fruit flies like a banana."

 

[mikepf]

Well, if you put =IIf([SSN]=50,99,[SSN]) in Control Source for SSN, then you get a green edge on the SSN field (in design) and #ERROR on the SSN field after opening up the form. Any thoughts?

 

[genomon]

Is this is in a text box on a form? What are you trying to do? Update a table, or display data?

"Time flies like an arrow; fruit flies like a banana."

 

[mikepf]

Yes, in a text box and update a table.

 

[genomon]

What are you trying to do? Update a table, or display data?

So you are using this as an input box to store data on a table? Is the form bound or unbound? If it's bound, you can add code to the BeforeUpdate() event, using the string functions that I mentioned above. If unbound, then use the SQL update code with the DoCmd object.

"Time flies like an arrow; fruit flies like a banana."

 

[PHV]

As you want to update a table why not simply use an update query ?
A starting point (SQL code):
UPDATE yourTable SET SSN=99 WHERE SSN=50

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mikepf]

Yes, the update query would be one way to do this but I wanted to try using an expression on the SSN field of the form. It appears nobody uses this kind of update because nobody has told me how to do it. I initially thought using Validation Rules was where the expression is used but am now thinking that it should be placed on an event change (like on-click). But so far even that doesn't work.