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

1. ## 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. ## 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))

3. ## 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. ## 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.

5. ## 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

#### Posting Permissions

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