Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200

    Extract certain values from field

    I want to be able to extract values from a field if they meet a certain criteria. These extracted values would then populate a different field. How can I accomplish this? Thanks.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Are you trying to take values from one field in a table and put them into a field in another table?

  3. #3
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    From one field in a form to another field in a form

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Are both Forms open at the same time?

  5. #5
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    Sorry for not being clearer....both fields are in the same form.

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    What action on your Form do you want to be the action that causes the value from one field be appllied to the other field?

    Eg:
    Say you have two fields on your Form named Field1 & Field2.
    Then let's say you have a command button called 'cmdUpdate'.
    If you type a value into Field1 and click cmdUpdate - the value from Field1 will show up in Field2.
    In order to do this - you can put this code behind your command button.

    Code:
     
    Private Sub cmdUpdate_Click()
    Dim strField1 As String
     
    Me.Field1.SetFocus
    strField1 = Me.Field1.Text
     
    Me.Field2.SetFocus
    Me.Field2.Text = strField1
     
    End Sub

  7. #7
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    Awesome! I'd like to be able to apply this to all the previous records in a one-time execution of the code. Would I just delete the button afterwards?

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Wait.
    Are you trying to change data in the Table behind the Form so that two different fields in the Table have the same data??

  9. #9
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    Trying to extract values that meet a certain criteria to be placed in another field on the form.

  10. #10
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'd like to be able to apply this to all the previous records in a one-time execution of the code.
    Where are all the previous records stored?
    The Form only 'displays' the data.
    Last edited by Robeen; 09-23-2011 at 12:57 PM. Reason: Incomplete.

  11. #11
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    In a table the form is based off of. But can I just extract the data from one field to populate another field I create on the form?

  12. #12
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    Would it be easier to make the change at the table/query level???

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Yes you can do this automatically so that every time you move to a new record on the Form - the value from Field1 is applied to Field2.

    Is your Form showing 1 record at a time? Or is it in Datasheet View?

    If you only want to display the same thing twice on the Form - but not change anything in the Table . . .

    I think the easiest way to do this is:
    The field in which you want to display the duplicate value from Field1:
    make its 'Control Source' Property - Field1.
    Then it will show the same thing.

    Or - if you want to get 'fancy':

    Create a query based on your Table so that it has all the fields that you are using on your Form.

    Code:
     
    Select Field1, Field2, Field3 . . . DuplilcateField: [Field1]
    From TableName
    The field 'DuplicateField' will have the same Value as Field1.

    Save your Query.

    Now go to your Form and change the Record Source Property of the Form to the query you just created.

    Now - when you run the Form - the Field on your Form that you link to 'DuplicateField' from your Query - will always show the same value as Field1.

  14. #14
    tylerg11 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    200
    Just shows one record at a time.

    To summarize:

    The form has a field called "Preferences:". If the data contained in that field matches a list of about 5 different values, i want those values to be extracted from that field and placed in a new field. If the data does not match my criteria nothing happens. Would this be an easier fix at the table/query level?

  15. #15
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you give me a simple example with data?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use a lookup field to influence values in another field
    By nathanrt in forum Database Design
    Replies: 3
    Last Post: 01-13-2011, 03:40 PM
  2. IIf using other field values in same query
    By ErikaMarie0421 in forum Queries
    Replies: 2
    Last Post: 08-03-2010, 12:21 PM
  3. Extract text from middle of a field
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-02-2009, 01:13 PM
  4. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 PM
  5. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums