Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: 'greater than' problem

    hi,

    im having trouble setting up a query in a sports day database i have to complete for school. in a form i have a combo box which allows the user to select an age group. a query is then performed using the selected age group and a report generated. the agre groups are under 13s, under 14s, under 15s, under 16s and open (open is anyone over 15 years old). the query for under 13 to under 15s works fine, however im not sure how to get the 'open' query to work. in the combo box i have the value for 'open' set as '>15', but access doesnt seem to like this and no records are selected from my tables, even though i have entries who have an age greater than 15.

    does anyone have an idea how i can solve this? i would prefer to use just one query which gets an age group from a form rather than have an individual query for each age group.

    thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Question how are you storing the value

    is the age a feild is a text feild or number feild

    or

    are caluateing base on the brith or the person
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2006
    Posts
    10
    the age is calculated by the date of birth of the person, but the query i am using just displays the age.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    What formula are you using to calculate age ?

  5. #5
    Join Date
    Sep 2006
    Posts
    10
    it just subtracts the birth date from the current date and divides by 365.2425.

    but the formula works out the ages correctly, the 'greater than' sign just isnt being interpreted by ms access correctly.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Are you able to post a sample of your d/b ?
    Chris

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Cool What about writing a function To display the Groups

    Code:
    Function Age_Group(age)
    Select Case Age
    Case 0 to 12
    Age_Group ="Under 13s"
    Case 12 to 13
    Age_Group ="Under 14s"
    Case 13 to 14
    Age_Group ="Under 15s"
    Case 14 to 16
    Age_Group ="Under 16s"
    Case Else
    Age_Group ="Open"
    End Select
    End Function
    Code off top of head

    Mite need to play a round the the Case ??

    to use

    paste code into module
    in Query

    My_Group:Age_Group([age])
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Sep 2006
    Posts
    10
    awesome, it works! thanks myle!

Posting Permissions

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