Forcing an expression to be a number, not a string

[John T Ingato]

I have included the SQL below:
This is a total query that calculates total dollars sold out of each of the
1800 stores. As some of the stores have just opened, I have calculated the
number of weeks reported using ([LastReportingDate]-[FirstReportingDate])/7
AS WeeksReported.

On the last line of the select clause I have ...
Format(Sum([DollarsSold])/[WeeksReported],"Currency") AS DollarsPerWeek ...
which divides the DollarsSold / WeeksReported then formated the expression
as currency. If try to sort ascending, I get a dialog asking for
WeeksReported. If I don't sort ascending and sort the recordset instead, it
sort the fields as string values:
2
21
22
3
34
Is there a way to force that expression to become a number instead of a
string? In the data set, the fields are left justified, so I know they are
strings.

********SQL*****************

SELECT
tblHomeDepotCalculatedSalesData.StoreNumber,
tblHomeDepotStoreList.City,
tblHomeDepotStoreList.State,
tblHomeDepotStoreList.MKT,
Min(tblHomeDepotCalculatedSalesData.FromDate) AS FirstReportingDate,
Max(tblHomeDepotCalculatedSalesData.ToDate) AS LastReportingDate,
([LastReportingDate]-[FirstReportingDate])/7 AS WeeksReported,
Sum(tblHomeDepotCalculatedSalesData.DollarsSold) AS SumOfDollarsSold,
Format(Sum([DollarsSold])/[WeeksReported],"Currency") AS DollarsPerWeek

FROM
tblMLOProductLine INNER JOIN (tblHomeDepotStoreList INNER JOIN
tblHomeDepotCalculatedSalesData

ON tblHomeDepotStoreList.StoreNumber =
tblHomeDepotCalculatedSalesData.StoreNumber)
ON tblMLOProductLine.ItemNumber = tblHomeDepotCalculatedSalesData.ItemNumber

GROUP BY tblHomeDepotCalculatedSalesData.StoreNumber,
tblHomeDepotStoreList.City, tblHomeDepotStoreList.State,
tblHomeDepotStoreList.MKT;

 

[Smartin]

I have included the SQL below:
This is a total query that calculates total dollars sold out of each of the
1800 stores. As some of the stores have just opened, I have calculated the
number of weeks reported using ([LastReportingDate]-[FirstReportingDate])/7
AS WeeksReported.

On the last line of the select clause I have ...
Format(Sum([DollarsSold])/[WeeksReported],"Currency") AS DollarsPerWeek ...
which divides the DollarsSold / WeeksReported then formated the expression
as currency. If try to sort ascending, I get a dialog asking for
WeeksReported. If I don't sort ascending and sort the recordset instead, it
sort the fields as string values:
2
21
22
3
34
Is there a way to force that expression to become a number instead of a
string? In the data set, the fields are left justified, so I know they are
strings.

The Format() function returns a string. Lose the Format() wrapper to
return the value.

I think I know what you're going to say next, "But I want it to look
like currency!" If you use a report, you can format it there. Or if you
take the query results to, say, Excel you could do the formatting there.

Maybe another way (not sure if this will work) would be to keep your
SELECT clause as is and add an ORDER BY clause such as

ORDER BY Sum([DollarsSold])/[WeeksReported] ASC

 

[John T Ingato]

Thanks guys,

Changing "Sum([DollarsSold])/[WeeksReported]" to
"CCur(Sum([DollarsSold])/[WeeksReported])" does allow me to sort the
datasheet after the query runs, however I still can not add ORDER BY
CCur(Sum([DollarsSold])/[WeeksReported]) ASC because when the query is run,
it asks me to enter "WeeksReported" value.

Any suggestions?

 

[Smartin]

Thanks guys,

Changing "Sum([DollarsSold])/[WeeksReported]" to
"CCur(Sum([DollarsSold])/[WeeksReported])" does allow me to sort the
datasheet after the query runs, however I still can not add ORDER BY
CCur(Sum([DollarsSold])/[WeeksReported]) ASC because when the query is run,
it asks me to enter "WeeksReported" value.

That's because I wasn't paying attention.

In your query "WeeksReported" is an alias for a calculation:

([LastReportingDate]-[FirstReportingDate])/7 AS WeeksReported

You can't reference the alias elsewhere in the query, but you can put
the defining calculation in its place:

ORDER BY
(([LastReportingDate]-[FirstReportingDate])/7) ASC


Hope that helps!

 

[RoyVidar]

Thanks guys,

Changing "Sum([DollarsSold])/[WeeksReported]" to
"CCur(Sum([DollarsSold])/[WeeksReported])" does allow me to sort the
datasheet after the query runs, however I still can not add ORDER BY
CCur(Sum([DollarsSold])/[WeeksReported]) ASC because when the query
is run, it asks me to enter "WeeksReported" value.

Any suggestions?

Agreed, formatting is for the 'front end', but ...

The *source* of the problem is that in Access/Jet division by an
INTEGER value causes the result to be coerced to DOUBLE FLOATING
POINT. Assuming the column 'DollarsSold' is CURRENCY

SELECT CCUR(1234.5678) AS DollarsSold, CLNG(52) AS WeeksReported,
DollarsSold / WeeksReported AS result_value,
TYPENAME(DollarsSold / WeeksReported) AS result_type

result_value = 4.50045
result_type = Double

The OP probably wants to cast the result to CURRENCY (or whatever
your original column was) e.g.

SELECT CCUR(234.0234) AS DollarsSold, CLNG(52) AS WeeksReported,
CCUR(DollarsSold / WeeksReported) AS result_value,
TYPENAME(CCUR(DollarsSold / WeeksReported)) AS result_type

result_value = 4.5004
result_type = Currency

The idea is that the 'front end' (form control, report field, etc)
can determine the default formatting based on the value being passed
through as the correct type.

FWIW the DECIMAL type, being Access/Jet's native 'decimal' type and
the one I use for money data, does not exhibit the 'coerce to float'
behaviour e.g.

SELECT 234.0234 AS DollarsSold, CLNG(52) AS WeeksReported,
DollarsSold / WeeksReported AS result_value,
TYPENAME(DollarsSold / WeeksReported) AS result_type

result_value = 4.50045
result_type = Decimal

Jamie.

As I've understood it, the ORDER BY clause is evaluated prior to the
SELECT clause, which makes the alias unknown at the that time, and
you will either have to repeat the whole calcualation in the ORDER BY
clause, or you could try sorting on ordinal position. With your initial
query, if I've counted correct

....ORDER BY 9

might work.

 

[RoyVidar]

However...


...why is it that the SQL-92 compliant construct that actually works
in Jet (i.e. ordinal position in resultset) is the least satisfactory
<g>?

Is it? I didn't know.

 

[RoyVidar]

Least satisfactory? That's a judgment, of course, but one which your
"if I've counted correct" comment would seem to concur.

I think you read to much between the lines.