Using find and replace in different fields.

[lostinseganet]

Hello everyone. I am trying to use access 2003 to see a word in one field and write a number in another field. Like if agree =3 , disagree= 1. I want access to see agree in a field and write 3 in another field. How can this be done?

For example if I had

Field 1 Field 2
Agree _____
disagree _____
agree _____
agree _____

I want access to realize that agree equals 3, and disagree equals 1
I want the result to look like

Field 1 Field 2
Agree 3
disagree 1
agree 3
agree 3


So in the end i only input the words strongly disagre, agree, disagree,
neutral, strongly agree (hate it, hate you...blah blah) (>^_^)>
and get whole numbers 1,2,3,4, or 5

[NeoPa]

Have you thought of using ComboBoxes with two columns - text and number columns. The text is displayed but the number is the associated value.
This seems ideal for your purpose.

[lostinseganet]

No I have not I have little experience programming for access 2003. I was trying to self teach myself with a book.

[NeoPa]

Would you like to check it out then?
You can let us know how you get on or if you find a problem with that (and what the problem is of course).

[missinglinq]

The advantage to NeoPa's suggestion is that the users get to select a comment, i.e. "agree" or "agree strongly" rather than having to type in an answer themselves.

You could use the AfterUpdate event for the comment field to assign the value to the second field,with something like this

Private Sub YourCommentField_AfterUpdate()
   Select Case YourCommentField
        Case "Agree Strongly"
     Me.CommentRatingField = 4
       Case "Agree"
         Me.CommentRatingField = 3
       Case "Disagree"
         Me.CommentRatingField = 2
       Case "Disagree Strongly"
         Me.CommentRatingField = 1
       Case Else
         Me.CommentRatingField = 0
  End Select
End Sub 

If the user then entered "Agree Strongly" the numerical field would be assigned the value 4. But if the user inadvertently entered "Strongly Agree" instead, an easy mistake to make, then the numerical field would be assigned the value of 0, which isn't what you intended! The combobox eliminates this type of mistake.

Welcome to TheScripts!

Linq ;0)>

[NeoPa]

Very well expressed Linq.
That was indeed behind what I was saying :)

[nico5038]

Hmm,the "normalized" way to solve this would be to create an additional table with two fields filled with the description and the value:
Description, [FieldValue]
Agree, 1
Disagree, 2
etc...
This could be used to store directly the FieldValue by defining the Field1 in the table definition as a "Lookup" field that extracts the value from this new lookup table.
Another option will be to JOIN the lookup table by the Description to the lookup table and use the "joined" FieldValue for your needed final "table" that's effectively realised by a query.

Nic;o)

[NeoPa]

Linq's post was illustrating the problems of doing it in a SELECT statement rather than suggesting it. The normalised way is indeed better, but if it's only used once I would find setting up a ComboBox with static entries quite acceptable.