ordering recordset by two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query which returns 3 fields:
product codes
price in $
price in Euro

Every product has its price either in $ or Euro (but not in both)

How do I order the recordset by the product price expressed in ? ?
If I pass the $-to-? and Euro-to? rated as parameters.
But how do I specify the ORDER BY clause ?

I appreciate that one of the solutions might be to restructure the table and
instead of fields "Price_$" and "Price_Euro" to make one field "Price" and
the other "Currency", but this has some unwanted side effects
 
Aa,

I suppose you could make a calculated field in a query, like this...

AbsolutePrice: Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]
.... and then just sort on this column.
 
thanks, Steve, sounds like a good idea. I'' try it

Steve Schapel said:
Aa,

I suppose you could make a calculated field in a query, like this...

AbsolutePrice: Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]
... and then just sort on this column.

--
Steve Schapel, Microsoft Access MVP

I have a query which returns 3 fields:
product codes
price in $
price in Euro

Every product has its price either in $ or Euro (but not in both)

How do I order the recordset by the product price expressed in ? ?
If I pass the $-to-? and Euro-to? rated as parameters.
But how do I specify the ORDER BY clause ?

I appreciate that one of the solutions might be to restructure the table and
instead of fields "Price_$" and "Price_Euro" to make one field "Price" and
the other "Currency", but this has some unwanted side effects
Click to expand...
Click to expand...
 
In
AbsolutePrice: Nz([Price in $],0)*[DollarRate]
What is Nz ?

Steve Schapel said:
Aa,

I suppose you could make a calculated field in a query, like this...

AbsolutePrice: Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]
... and then just sort on this column.

--
Steve Schapel, Microsoft Access MVP

I have a query which returns 3 fields:
product codes
price in $
price in Euro

Every product has its price either in $ or Euro (but not in both)

How do I order the recordset by the product price expressed in ? ?
If I pass the $-to-? and Euro-to? rated as parameters.
But how do I specify the ORDER BY clause ?

I appreciate that one of the solutions might be to restructure the table and
instead of fields "Price_$" and "Price_Euro" to make one field "Price" and
the other "Currency", but this has some unwanted side effects
Click to expand...
Click to expand...
 
I am not familiar with Access wizards and do all the query manually in SQL
Is it correct to think, that a calculated file is added like:

SELECT [Price in $]*[DollarRate] + [Price in Euro],0*[EuroRate] AS
My_Calculated_Field

?

Steve Schapel said:
Aa,

I suppose you could make a calculated field in a query, like this...

AbsolutePrice: Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]
... and then just sort on this column.

--
Steve Schapel, Microsoft Access MVP

I have a query which returns 3 fields:
product codes
price in $
price in Euro

Every product has its price either in $ or Euro (but not in both)

How do I order the recordset by the product price expressed in ? ?
If I pass the $-to-? and Euro-to? rated as parameters.
But how do I specify the ORDER BY clause ?

I appreciate that one of the solutions might be to restructure the table and
instead of fields "Price_$" and "Price_Euro" to make one field "Price" and
the other "Currency", but this has some unwanted side effects
Click to expand...
Click to expand...
 
Aa,

Yes, that is correct. In the example I gave, the SQL would look like...
SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice

Nz() is a function which returnd a zero in the case of Null. I think
you will need it in yout expression... as I understand it, each record
has either a Price in $ or a Price in Euro but not both, so without the
Nz() the expression will always evaluate to Null.

By the way, as an aside, it is not a good idea to use a $ as part of a
field name.

You ought to try Access's query design view :-)
 
Thanks, Stive
The problem with that syntax is that when I try to order it by that
calculated field using

SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
From ....
WHEN....
ORDER BY [AbsolutePrice]

It consideres AbsolutePrice as a parameter name and prompts for its value.
How should I change the syntax?


Steve Schapel said:
Aa,

Yes, that is correct. In the example I gave, the SQL would look like...
SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice

Nz() is a function which returnd a zero in the case of Null. I think
you will need it in yout expression... as I understand it, each record
has either a Price in $ or a Price in Euro but not both, so without the
Nz() the expression will always evaluate to Null.

By the way, as an aside, it is not a good idea to use a $ as part of a
field name.

You ought to try Access's query design view :-)

--
Steve Schapel, Microsoft Access MVP

I am not familiar with Access wizards and do all the query manually in SQL
Is it correct to think, that a calculated file is added like:

SELECT [Price in $]*[DollarRate] + [Price in Euro],0*[EuroRate] AS
My_Calculated_Field

?
Click to expand...
Click to expand...
 
Aa,

I think you have to do it like this...
SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
FROM ....
WHERE....
ORDER BY Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]
 
Thanks,
But in this case
Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
Click to expand...

becomes exxeccive if as I want the recordsed ordered by this value, but I do
not need the value
Therefore

SELECT *
FROM ....
WHERE....
ORDER BY Nz([Price in $],0)*[DollarRate] + Nz([Price in Euro],0)*[EuroRate]

should suffice, should not it - at least it seem the produce the same order



Steve Schapel said:
Aa,

I think you have to do it like this...
SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
FROM ....
WHERE....
ORDER BY Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]

--
Steve Schapel, Microsoft Access MVP

Thanks, Stive
The problem with that syntax is that when I try to order it by that
calculated field using

SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
From ....
WHEN....
ORDER BY [AbsolutePrice]

It consideres AbsolutePrice as a parameter name and prompts for its value.
How should I change the syntax?
Click to expand...
Click to expand...
 
The bit
Nz([Price in $],0)*[DollarRate] + Nz([Price in Euro],0)*[EuroRate]
consists of arithmetical operations.
Is it possible to introduce some logic like IF into it?
In sertain records I habe both $ and Euro. Therefore for such records I need
to consider, say, just $ price for ORDER BY purpose ?

Steve Schapel said:
Aa,

I think you have to do it like this...
SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
FROM ....
WHERE....
ORDER BY Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate]

--
Steve Schapel, Microsoft Access MVP

Thanks, Stive
The problem with that syntax is that when I try to order it by that
calculated field using

SELECT Nz([Price in $],0)*[DollarRate] + Nz([Price in
Euro],0)*[EuroRate] AS AbsolutePrice
From ....
WHEN....
ORDER BY [AbsolutePrice]

It consideres AbsolutePrice as a parameter name and prompts for its value.
How should I change the syntax?
Click to expand...
Click to expand...
 
Aa,

Try this...
Nz([Price in $],0)*[DollarRate] + IIf(IsNull([Price in $],Nz([Price in
Euro],0)*[EuroRate],0)
 
Thanks, Steve.

This runs correcly from within Access2000
Yet when run from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?



Steve Schapel said:
Aa,

Try this...
Nz([Price in $],0)*[DollarRate] + IIf(IsNull([Price in $],Nz([Price in
Euro],0)*[EuroRate],0)

--
Steve Schapel, Microsoft Access MVP

The bit
Nz([Price in $],0)*[DollarRate] + Nz([Price in Euro],0)*[EuroRate]
consists of arithmetical operations.
Is it possible to introduce some logic like IF into it?
In sertain records I habe both $ and Euro. Therefore for such records I need
to consider, say, just $ price for ORDER BY purpose ?
Click to expand...
Click to expand...
 
Aa,

I'm sorry, I don't know anything about ASP, and I don't know what is
causing this problem. Maybe you could post it back as a new thread,
maybe in the microsoft.public.access.internet newsgroup.
 
Back
Top