Auto Function

[Anthony]

Is there a way tin access if a certain field in a table is something that it
will automatically fill something in another field. If a value within a
table is 90 to 95 I would like it to fill in the value of 4 and if it is 96
to 100 to fill in a value of 5. Is there anyway for this to be accomplished.
Thanks

 

[fredg]

Is there a way tin access if a certain field in a table is something that it
will automatically fill something in another field. If a value within a
table is 90 to 95 I would like it to fill in the value of 4 and if it is 96
to 100 to fill in a value of 5. Is there anyway for this to be accomplished.
Thanks

You would not want to do this in the table, but it's easy enough to do
in a query of in a form or report.

For example, in a query:
NewColumnName:IIf([FieldA] between 90 and 95,4,IIf([FieldA] between 96
and 100,5,Null))

However, what do you wish to show if it's less than 90 or more than
100? Replace Null with whatever other value you wish to display.

 

[Evi]

If you actually want the data added to a field in your table rather than
being stored as a calculation in a query then you use the After Update Event
in your form.
(You ARE always inputting your data via a form, like a good Accesser,
aren't you!)
Open your form in Design View.
that has the field name which you want to type stuff into.
We'll call it the field that you want to type stuff into MyGrade
We'll call the field which you want to be filled in automatically My Result
Click on MyGrade
Click on the Properties button.
Click on the Event tab
Click next to the line that says After Update
(this means that you want something to happen after you have finished typing
something into that field).
Choose Event Procedure.
Click just right of that and a Code Page will open with 2 lines on it, the
bottom line will be
End sub

Just above that put something like this -
I'm using a simplified way of doing this so you can get the idea of how it
works but there are neater ways

If Me.MyGrade >=90 AND Me.MyGrade<=95 Then

Me.MyResult = 4

ElseIf Me.MyGrade >=96 AND Me.MyGrade <=100 Then
Me.MyResult = 5
End IF

I suspect that you are grading stuff, say exam results. If that is the case,
then the best way to do this is to have a table that keeps grades with the
Maximum and Minimum scores.
Then I would add code which looks up that grade and writes it into your
table.

This is one of those cases where it is a good idea to put the results in a
field in your table rather than using a calculation in a query because the
criteria for your grade might change from one year to the next and then all
your past data will look wrong.
The other scenario for putting items into a table is when logic suggests
that a certain value should go in a field but every now and again the user
wants to put something else there.

Evi

 

[Anthony]

I keep getting an error after I enter the information in the criteria
section. The error message. As soon as I run it again I will post. Thanks

Is there a way tin access if a certain field in a table is something that it
will automatically fill something in another field. If a value within a
table is 90 to 95 I would like it to fill in the value of 4 and if it is 96
to 100 to fill in a value of 5. Is there anyway for this to be accomplished.
Thanks

You would not want to do this in the table, but it's easy enough to do
in a query of in a form or report.

For example, in a query:
NewColumnName:IIf([FieldA] between 90 and 95,4,IIf([FieldA] between 96
and 100,5,Null))

However, what do you wish to show if it's less than 90 or more than
100? Replace Null with whatever other value you wish to display.