Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate field depending on a number (Access 2000)

    How can i populate a field depending on a number ? In my field city the entries begin with numbers. I want the folloiwng : if the number is between 5000 and 6000 to populate with 1 the field Region.If the number is between 6000 and 7000, to populate the field with the number 2.And so on, i have 10 regions

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate field depending on a number (Access 2000)

    Create a query based on your table.
    Add the region field to the query grid.
    Select Query | Update Query.
    Enter the following expression in the 'Update to' field:
    <code>
    Val([city])1000-4
    </code>
    Select Query | Run to execute the query.

    Explanation: Val([city]) extracts the number from the beginning of the city field.
    1000 divides the number by 1000, performing integer division, i.e. the 6789 1000 = 6.
    We subtract 4 from the result because you want 5000 to become 1, etc.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate field depending on a number (Access 2000)

    Thank you for your reply. I am so sorry my question was not so well defined. I have given only a rough example for the figures and i have mislead you.These figures do not always follow the logic. Also I would like to modify and mend the function myslef depending on the information about the figures received subsequently.Roughly speaking the function should look like this :
    If the figure is between 1000 and 2000 Then region = 1
    If the figure is between 3000 and 4000 then the region = 2
    If the figure is between 8000 and 9000 then the region = 3
    However I need to be more flexible and to be able to change or add the numbers in the function In order to customize it to some of the figures.Could you help me build such a function ? Maybe I should build a form first ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate field depending on a number (Access 2000)

    You see how important it is to provide correct information!
    You can create a function in a standard module (created by clicking New in the Modules section of the database window):
    <code>
    Public Function GetRegion(strCity As String) As Integer
    Dim lngValue As Long
    lngValue = Val(strCity)
    If lngValue >= 1000 And lngValue < 2000 Then
    GetRegion = 1
    ElseIf lngValue >= 3000 And lngValue < 4000 Then
    GetRegion = 2
    ElseIf lngValue >= 8000 And lngValue < 9000 Then
    GetRegion = 3
    End If
    End Function
    </code>
    Modify the function and add new ElseIf parts as needed. You can use the functiion in the 'Update to' line of the update query:
    <code>
    GetRegion([city])
    </code>
    Warning: make sure that all possible values are covered. The function will return 0 for values that are not handled in the If ... End If part.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate field depending on a number (Access 2000)

    Thank you very much! I think i cannot use properly your wonderful suggestion. Somehow cannot create the command. Would would you look up my command ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate field depending on a number (Access 2000)

    Your use of the function makes no sense:

    Private Sub Command0_Click()
    GetRegion ([activities].[city])
    End Sub

    is meaningless. As I wrote, you can use the function in the Update to line of an update query.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate field depending on a number (Access 2000)

    Thank you, yes i get the result now. I wonder how clever this way of updating is.Thank you

  8. #8
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate field depending on a number (Access 2000)

    You were right warning me that all possible values should be covered.Is it possible to filter the query so as not to update the 0 values? .Now together with the relevant values of the region i get also the 0 values

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate field depending on a number (Access 2000)

    You can add a calculated column to the query:
    <code>
    R: GetRegion([city])
    </code>
    Enter
    <code>
    Not 0
    </code>
    in the Criteria line for this column.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •