Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Function in Query? (A2k (9.0.3821) SR-1)

    Formatting Function in Query?

    A2k (9.0.3821) SR-1

    I have the following call to a function in a query and it works OK except for formatting.

    Days: IIf(Nz([Certificate])="","Open",Right("000" & NumberOfDays([Begin],[Certificate],True),4))

    This is what it returns:

    0042 (42 days to complete)
    Open (No Certificate Date)
    0001
    0123
    Open
    0058

    I

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Function in Query? (A2k (9.0.3821) SR-1)

    If Numberofdays return a string use
    Days: IIf(Nz([Certificate])="","Open",Val(Right("000" & NumberOfDays([Begin],[Certificate],True),4)))

    If If Numberofdays return a numeric use
    Days: IIf(Nz([Certificate])="","Open",NumberOfDays([Begin],[Certificate],True))
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Function in Query? (A2k (9.0.3821) SR-1)

    Hi Francois

    Its numeric and the following still returns left justified, I'm looking for right justified.

    If If Numberofdays return a numeric use
    Days: IIf(Nz([Certificate])="","Open",NumberOfDays([Begin],[Certificate],True))

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting Function in Query? (A2k (9.0.3821) SR-1)

    The field is based on an expression. You're returning text values as part of that expression. That makes it a text field and it's going to left justify.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Function in Query? (A2k (9.0.3821) SR-1)

    Sorry, I misunderstand the question.
    Use the same function in the query.
    Select this column.
    From The menu view select properties.
    In the Format Property enter General Number.

    If you want to sort the query on this column, but in numeric order, create a new column and enter the this funtion
    SortDays: Val(IIf(Nz([Certificate])="","Open",NumberOfDays([Begin],[Certificate],True)))
    and sort on this new column. If you don't want to display this new column, uncheck the Show checkbox
    Francois

Posting Permissions

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