Expression in Query. Weird sort problem

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

Guest

Hi,

Using a linked table, I have a query with 5 normal fields, and 3 calculated
fields. They are as such:
calDate: Left([SubmitTime],9)
milDate: Format([timeDate],"hhnnss")
timeDate: Right([SubmitTime],11)

Now. When I leave "milDate" unsorted, the query runs fine, and the parameter
pop up works fine for one of the normal fields. When I try to sort it as
descending [which is what I need], the query seems to think the [timeDate] is
a parameter, and it pops up a box. I do not want this to happen, and if I
leave it blank, the call fails.

How can I get this to sort correctly but not totally fail my query?
 
You need to put the full expression for milDate in the ORDER BY clause, not
the alias:

SELECT *, Left([SubmitTime],9) AS calDate,
Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM TableName
ORDER BY Format([timeDate],"hhnnss");
 
Here is my query:
SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format([timeDate],"hhnnss") DESC;

Maybe I missed it, but is there a difference? It looks like the elements of
your post were already in my query. If you can point out a difference, that'd
be appreciated.

Chris

Ken Snell (MVP) said:
You need to put the full expression for milDate in the ORDER BY clause, not
the alias:

SELECT *, Left([SubmitTime],9) AS calDate,
Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM TableName
ORDER BY Format([timeDate],"hhnnss");

--

Ken Snell
<MS ACCESS MVP>


Chris C said:
Hi,

Using a linked table, I have a query with 5 normal fields, and 3
calculated
fields. They are as such:
calDate: Left([SubmitTime],9)
milDate: Format([timeDate],"hhnnss")
timeDate: Right([SubmitTime],11)

Now. When I leave "milDate" unsorted, the query runs fine, and the
parameter
pop up works fine for one of the normal fields. When I try to sort it as
descending [which is what I need], the query seems to think the [timeDate]
is
a parameter, and it pops up a box. I do not want this to happen, and if I
leave it blank, the call fails.

How can I get this to sort correctly but not totally fail my query?
Click to expand...
Click to expand...
 
timeDate is also an alias in your query, so that is why you're getting that
parameter window.... try this:

SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format(Right([SubmitTime],11),"hhnnss") AS
milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format(Right([SubmitTime],11),"hhnnss") DESC;

--

Ken Snell
<MS ACCESS MVP>



Chris C said:
Here is my query:
SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format([timeDate],"hhnnss") DESC;

Maybe I missed it, but is there a difference? It looks like the elements
of
your post were already in my query. If you can point out a difference,
that'd
be appreciated.

Chris

Ken Snell (MVP) said:
You need to put the full expression for milDate in the ORDER BY clause,
not
the alias:

SELECT *, Left([SubmitTime],9) AS calDate,
Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM TableName
ORDER BY Format([timeDate],"hhnnss");

--

Ken Snell
<MS ACCESS MVP>


Chris C said:
Hi,

Using a linked table, I have a query with 5 normal fields, and 3
calculated
fields. They are as such:
calDate: Left([SubmitTime],9)
milDate: Format([timeDate],"hhnnss")
timeDate: Right([SubmitTime],11)

Now. When I leave "milDate" unsorted, the query runs fine, and the
parameter
pop up works fine for one of the normal fields. When I try to sort it
as
descending [which is what I need], the query seems to think the
[timeDate]
is
a parameter, and it pops up a box. I do not want this to happen, and if
I
leave it blank, the call fails.

How can I get this to sort correctly but not totally fail my query?
Click to expand...
Click to expand...
Click to expand...
 
Made sense looking at it but the problem still didn't fix.

For some addresses, the sorting is correct, for others, it isn't. It seems
as though the "Left([SubmitTime],9)" field, which represents calDate, still
isn't sorting correctly even though milDate appears to be.

On a plus side, the parameter problem was fixed, so cheers for that. But
this sorting is really the essential piece. I can't keep going until it's
right. Any ideas on how to get both calDate AND milDate to sort correctly
[descending]?

Chris

Ken Snell (MVP) said:
timeDate is also an alias in your query, so that is why you're getting that
parameter window.... try this:

SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format(Right([SubmitTime],11),"hhnnss") AS
milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format(Right([SubmitTime],11),"hhnnss") DESC;

--

Ken Snell
<MS ACCESS MVP>



Chris C said:
Here is my query:
SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format([timeDate],"hhnnss") DESC;

Maybe I missed it, but is there a difference? It looks like the elements
of
your post were already in my query. If you can point out a difference,
that'd
be appreciated.

Chris

Ken Snell (MVP) said:
You need to put the full expression for milDate in the ORDER BY clause,
not
the alias:

SELECT *, Left([SubmitTime],9) AS calDate,
Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM TableName
ORDER BY Format([timeDate],"hhnnss");

--

Ken Snell
<MS ACCESS MVP>


Hi,

Using a linked table, I have a query with 5 normal fields, and 3
calculated
fields. They are as such:
calDate: Left([SubmitTime],9)
milDate: Format([timeDate],"hhnnss")
timeDate: Right([SubmitTime],11)

Now. When I leave "milDate" unsorted, the query runs fine, and the
parameter
pop up works fine for one of the normal fields. When I try to sort it
as
descending [which is what I need], the query seems to think the
[timeDate]
is
a parameter, and it pops up a box. I do not want this to happen, and if
I
leave it blank, the call fails.

How can I get this to sort correctly but not totally fail my query?
Click to expand...
Click to expand...
Click to expand...
 
When you say "sort correctly", should I assume that the data should sort
based on the actual time value? The way the query is constructed right now
it will sort based on "text" sorting and not "numeric" sorting -- this is
because the Left and Format actions return text strings, which sort left to
right based on the 0 to 9 heirarchy (e.g., 100 sorts as a lower value than
2).

If this is correct, then you need to convert the data back to a time data.
You'll need to show us some examples of the data and why you're having to
use Left etc. functions to extract the desired data. There may be a better
way, but we'll need to see the data first. Just show us some examples of the
data that are in the SubmitTime field and what you want to extract from
those values.

--

Ken Snell
<MS ACCESS MVP>

Chris C said:
Made sense looking at it but the problem still didn't fix.

For some addresses, the sorting is correct, for others, it isn't. It seems
as though the "Left([SubmitTime],9)" field, which represents calDate,
still
isn't sorting correctly even though milDate appears to be.

On a plus side, the parameter problem was fixed, so cheers for that. But
this sorting is really the essential piece. I can't keep going until it's
right. Any ideas on how to get both calDate AND milDate to sort correctly
[descending]?

Chris

Ken Snell (MVP) said:
timeDate is also an alias in your query, so that is why you're getting
that
parameter window.... try this:

SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit, dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format(Right([SubmitTime],11),"hhnnss")
AS
milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format(Right([SubmitTime],11),"hhnnss") DESC;

--

Ken Snell
<MS ACCESS MVP>



Chris C said:
Here is my query:
SELECT TOP 25 PERCENT dbo_vw_ReportData.BUnit,
dbo_vw_ReportData.Address,
dbo_vw_ReportData.City, dbo_vw_ReportData.State, dbo_vw_ReportData.ZIP,
Left([SubmitTime],9) AS calDate, Format([timeDate],"hhnnss") AS
milDate,
Right([SubmitTime],11) AS timeDate
FROM dbo_vw_ReportData
WHERE (((dbo_vw_ReportData.BUnit)=[Enter Biz Unit]))
ORDER BY dbo_vw_ReportData.Address, Left([SubmitTime],9) DESC ,
Format([timeDate],"hhnnss") DESC;

Maybe I missed it, but is there a difference? It looks like the
elements
of
your post were already in my query. If you can point out a difference,
that'd
be appreciated.

Chris

:

You need to put the full expression for milDate in the ORDER BY
clause,
not
the alias:

SELECT *, Left([SubmitTime],9) AS calDate,
Format([timeDate],"hhnnss") AS milDate,
Right([SubmitTime],11) AS timeDate
FROM TableName
ORDER BY Format([timeDate],"hhnnss");

--

Ken Snell
<MS ACCESS MVP>


Hi,

Using a linked table, I have a query with 5 normal fields, and 3
calculated
fields. They are as such:
calDate: Left([SubmitTime],9)
milDate: Format([timeDate],"hhnnss")
timeDate: Right([SubmitTime],11)

Now. When I leave "milDate" unsorted, the query runs fine, and the
parameter
pop up works fine for one of the normal fields. When I try to sort
it
as
descending [which is what I need], the query seems to think the
[timeDate]
is
a parameter, and it pops up a box. I do not want this to happen, and
if
I
leave it blank, the call fails.

How can I get this to sort correctly but not totally fail my query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top