Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Expression Builder . . . IF THEN ELSE? (2003)

    I have a Field that is a person's age as at joining (made up from an Expression using Date of Birth - Date of Entry, etc. This returns values like 23.49, etc.)

    I would like to band people together into 3 bands:
    Under 20
    20-30
    31+

    I thought I might be able to create an Expression that says If [age] < 21.00 THEN Under 21, elseif [age]>20.99 AND <31 THEN 20-30 else 31+

    I tried to enter this into a Query so that I could then have these as Group Headings in a Report, but I'm getting the feeling that I can't put IF THEN ELSE into the Expression Builder. Is that correct? If so, what's the best way to create a Field upon which I can Group a Report?

    Thanks in advance.

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

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    If ... Then ... Else is VBA. In expressions, you can't use that, but you can use the IIF (Immediate If) function instead. You could create two calculated fields:

    AgeGroup: IIf([Age]<21,1,IIf([Age]<31,2,3))

    AgeDesc: IIf([Age]<21,"Under 21",IIf([Age]<31,"20-30","31+")

    Use the AgeGroup field to group the report on - the number values will be soted in the correct order - but put a text box bound to AgeDesc in the group header (or footer), so that the descriptive text will be displayed/printed.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    Excellent, Hans, as usual.

    Quick Question. Why when I put Nz in front of the fields does Access not show blanks as 0? Have it like this
    Under21: Nz([Under 21])

    Thanks again.

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

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    Is Under21 a field?

    Anyway, try specifying the replacement value explicitly: Nz([Under21],0)

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    Yes, It's a Field (in a Query, not in a table), and I tried the syntax you supplied also, but I get an Error that it's not reconginzed as a Field or Expression?

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

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    Perhaps Access is getting confused because you're giving the column the same name as the field. Try

    Under21: Nz([QueryName].[Under21].0)

    where QueryName is the name of the query that has the Under21 field.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    No, that didn't work either. Is it because it's a Crosstab? Maybe it's too difficult to evaluate or something?

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

    Re: Expression Builder . . . IF THEN ELSE? (2003)

    Try creating the crosstab without Nz, then create a new query based on the crosstab and use Nz there.

    If that doesn't work either: could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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