Format Currency

[Guest]

The following scripts are in 'Make Table' queries. Each script is identical
in function except one multiples by 90% and the other multiplies by 100%.
The 100% when the table is created leaves the field format at currency.
However, the one with 90% changes it to a number format. Any reasoning
behind this?

SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, Sum([Cust AR
Balance]*0.9) AS [Six Forecast] INTO [Coll 6]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;


SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Four, Sum([Cust AR
Balance]*1) AS [Four Forecast] INTO [Coll 4]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="4") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;

Thanks.

 

[Douglas J Steele]

Easiest solution would be to use the CCur function:

SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, CCur(Sum([Cust
AR
Balance]*0.9)) AS [Six Forecast] INTO [Coll 6]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;

 

[John Vinson]

The following scripts are in 'Make Table' queries. Each script is identical
in function except one multiples by 90% and the other multiplies by 100%.
The 100% when the table is created leaves the field format at currency.
However, the one with 90% changes it to a number format. Any reasoning
behind this?

Any multiplication or division operation converts the values to Double
(to avoid losing precision). Wrap the expression in CCur() to convert
the Double result back to Currency.

One question: Why a MakeTable query? You can base a Form, Report,
export, new query, ..., directly on a Select query; it's very rare
that there is any real need to go through the complexity and overhead
of creating a new table!

John W. Vinson[MVP]

 

[Guest]

Douglas,

Thank you, it worked great.
--
Gina

Easiest solution would be to use the CCur function:

SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, CCur(Sum([Cust
AR
Balance]*0.9)) AS [Six Forecast] INTO [Coll 6]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following scripts are in 'Make Table' queries. Each script is identical
in function except one multiples by 90% and the other multiplies by 100%.
The 100% when the table is created leaves the field format at currency.
However, the one with 90% changes it to a number format. Any reasoning
behind this?

SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, Sum([Cust AR
Balance]*0.9) AS [Six Forecast] INTO [Coll 6]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;


SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Four, Sum([Cust AR
Balance]*1) AS [Four Forecast] INTO [Coll 4]
FROM ARBAL
GROUP BY ARBAL.Collector
HAVING (((ARBAL.Collector)="4") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
WITH OWNERACCESS OPTION;

Thanks.

 

[Guest]

When I tried to creat a report based on the query, none of the data would
show. So I thought it might be because the data was calculated and not stat.
Not really sure, I was just trying an alternative since it didn't work the
Form based off the query way. I have created many reports based off queries,
so I am not really sure why the data wouldn't show here.

 

[John Vinson]

When I tried to creat a report based on the query, none of the data would
show. So I thought it might be because the data was calculated and not stat.
Not really sure, I was just trying an alternative since it didn't work the
Form based off the query way. I have created many reports based off queries,
so I am not really sure why the data wouldn't show here.

Hrm. Maybe it's a parameter query (with a form reference as a
criterion maybe?) and the parameter wasn't available, or wasn't
correct?

John W. Vinson[MVP]