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

 

[Jamie Collins]

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
likecurrency!" 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.

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.

--

 

[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.

 

[Jamie Collins]

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

This is another example of how Jet SQL is non-compliant with the
SQL-92 standard. The ORDER BY should be processed last and should only
use the fields in the resultset, meaning column correlation names
('alias') where used.

The following constructs violate standards but work in Jet:

SELECT FirstName AS employee_name
FROM Employees
ORDER BY LastName;
-- 'LastName' is not in resultset so should not be available in ORDER
BY

SELECT FirstName AS employee_name
FROM Employees
ORDER BY FirstName;
-- 'FirstName' is not in resultset so should not be available in ORDER
BY

The following construct is compliant with standards but does not work
in Jet:

SELECT FirstName AS employee_name
FROM Employees
ORDER BY employee_name;

However...

ORDER BY 9

....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>?

Jamie.

--

 

[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.

 

[Jamie Collins]

Is it? I didn't know.

SQL-92 compliant? Yes. See 13.1 <declare cursor> (remember in the
standards ORDER BY is part of a cursor):

<order by clause> ::=
ORDER BY <sort specification list>

<sort specification list> ::=
<sort specification> [ { <comma> <sort
specification> }... ]

<sort specification> ::=
<sort key> [ <collate clause > ] [ <ordering
specification> ]

<sort key> ::=
<column name>
| <unsigned integer>

....If a <sort specification> contains an <unsigned integer>, then the
<unsigned integer> shall be greater than 0 and not greater than the
degree of T. The <sort specification> identifies the column of T with
the ordinal position specified by the <unsigned integer>.

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

Jamie.

--

 

[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.

 

[Jamie Collins]

I think you read to much between the lines.

I note you still haven't explicitly stated a preference here but a
quick google of your posts suggests that you don't (publicly) favour
ORDER BY <unsigned integer>; I don't see you saying "if I've spelt
correct" when using ORDER BY <column name>, either ;-)

Jamie.

--

 

[Jamie Collins]

ORDER BY 9

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

Further thoughts:

CREATE TABLE Months (short_name CHAR(3) NOT NULL)
;
INSERT INTO Months (short_name) VALUES ('Jan')
;
INSERT INTO Months (short_name) VALUES ('Feb')
;
INSERT INTO Months (short_name) VALUES ('Mar')
;
INSERT INTO Months (short_name) VALUES ('Apr')
;

Note the order of insertion (Jan, Feb, Mar, Apr) is significant.

SELECT short_name
FROM Months;
-- Resultset: (Jan, Feb, Mar, Apr)
With no explicit ORDER BY, I get the rows in ascending date/time order
of insertion. This is documented behaviour.

SELECT short_name
FROM Months
ORDER BY 1 ASC;
-- Resultset: (Apr, Feb, Mar, Jan)
With an explicit ORDER BY, I get the rows in alphabetical order. This
is supported behaviour.

But what about this one:

SELECT short_name
FROM Months
ORDER BY CLNG(1) ASC;
-- Resultset: (Apr, Mar, Feb, Jan)

The resultset always has the same order, regardless of the constant
value (e.g. CLNG(-1), CCUR(99.99) DESC, INSTR(short_name, CHR(22)),
etc), collation, engine, etc. But what determines this undocumented
order: descending date/time order of insertion?

Jamie.

--