format number

G

Guest

hi,

I have the following query:

SELECT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Sum(Tbl_CACS_calls_between_popenterdt_and_resolveddt.[COUNT( * )]) AS
[SumOfCOUNT( * )], "line1" AS line
FROM Tbl_Resolved INNER JOIN
Tbl_CACS_calls_between_popenterdt_and_resolveddt ON Tbl_Resolved.[Loan Acct
#] = Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #])
AS [SumOfCountOfLoan Acct #],
"line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
WHERE (((tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus)
Like "curr_pd*"))
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
UNION ALL
SELECT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Count(Tbl_Resolved.[Loan Acct #]) AS [CountOfLoan Acct #],
"line3" as line
FROM Tbl_Resolved LEFT JOIN Tbl_CACS_calls_between_popenterdt_and_resolveddt
ON Tbl_Resolved.[Loan Acct #] =
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]) Is Null))
UNION ALL
SELECT DISTINCT
Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Avg([resolveddt]-[popenterdt]) AS days,
"line4" AS line
FROM Tbl_Resolved
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_Resolved.ResolvedStatus
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT [1calltotals2].ResolvedStatus, [1calltotals2].ResolvedDt,
[1calltotals2].average_no_calls_to_be_resolved,
"line5" as line
FROM 1calltotals2;

I am trying to format the following so that only one decimal place is shown.
Currently there are 3 or 4 decimal places:

1. Avg([resolveddt]-[popenterdt]) AS days
2. [1calltotals2].average_no_calls_to_be_resolved

With Union queries, you cant go to the design view. I can only view in SQL
view, so I am not sure how I can change the formatting of these numbers to
show only 1 decimal place.

How can I do this?

Thanks in advance,
geebee
 
G

Guest

Try this --
Format(Avg([resolveddt]-[popenterdt]),"#.#") AS days

Format([1calltotals2].average_no_calls_to_be_resolved,"#.#")


geebee said:
hi,

I have the following query:

SELECT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Sum(Tbl_CACS_calls_between_popenterdt_and_resolveddt.[COUNT( * )]) AS
[SumOfCOUNT( * )], "line1" AS line
FROM Tbl_Resolved INNER JOIN
Tbl_CACS_calls_between_popenterdt_and_resolveddt ON Tbl_Resolved.[Loan Acct
#] = Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #])
AS [SumOfCountOfLoan Acct #],
"line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
WHERE (((tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus)
Like "curr_pd*"))
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
UNION ALL
SELECT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Count(Tbl_Resolved.[Loan Acct #]) AS [CountOfLoan Acct #],
"line3" as line
FROM Tbl_Resolved LEFT JOIN Tbl_CACS_calls_between_popenterdt_and_resolveddt
ON Tbl_Resolved.[Loan Acct #] =
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]) Is Null))
UNION ALL
SELECT DISTINCT
Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Avg([resolveddt]-[popenterdt]) AS days,
"line4" AS line
FROM Tbl_Resolved
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_Resolved.ResolvedStatus
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT [1calltotals2].ResolvedStatus, [1calltotals2].ResolvedDt,
[1calltotals2].average_no_calls_to_be_resolved,
"line5" as line
FROM 1calltotals2;

I am trying to format the following so that only one decimal place is shown.
Currently there are 3 or 4 decimal places:

1. Avg([resolveddt]-[popenterdt]) AS days
2. [1calltotals2].average_no_calls_to_be_resolved

With Union queries, you cant go to the design view. I can only view in SQL
view, so I am not sure how I can change the formatting of these numbers to
show only 1 decimal place.

How can I do this?

Thanks in advance,
geebee
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

format number 3
UNION QUERY 1
query prompts 2
add column 3
query error 3
query from form 8
day before 1
Query Error 2

Top