Having problem with a RowSource SQL statement

  • Thread starter Thread starter SirPoonga
  • Start date Start date
S

SirPoonga

I have a Thickness field in a table that is of type Number When I do
an ORDER BY it is ordering it like it was text. For example
1.5
1
12
13
2.4
2
24
26
....

Is this because I unioned it with an '*'.

Here's the SQL statment
SELECT '*' as Thickness
FROM MyTable
UNION
(SELECT DISTINCT MyTableThickness
FROM MyTable
ORDER BY Thickness)
 
SirPoonga said:
I have a Thickness field in a table that is of type Number When I do
an ORDER BY it is ordering it like it was text. For example
1.5
1
12
13
2.4
2
24
26
...

Is this because I unioned it with an '*'.

Here's the SQL statment
SELECT '*' as Thickness
FROM MyTable
UNION
(SELECT DISTINCT MyTableThickness
FROM MyTable
ORDER BY Thickness)
Click to expand...

I imagine so. After all, '*' can't be a number, it has to be text. So
the Thickness field returned by the query has to be text, too. You
might return two columns for use in the combo or list box, one column
being numeric (to be the bound column) and one column being text (to be
the displayed column). Something like this:

SELECT
MyTableThickness As Thickness,
CStr(MyTableThickness) As ThicknessDisplay
FROM MyTable
UNION
SELECT Null As Thickness, '*' as ThicknessDisplay
FROM MyTable
ORDER BY Thickness;

Code that makes use of the combo/list box's value would check to see if
the value is Null and interpret that appropriately.
 
Thanks, that makes sense.
Heh, I forgot to put a period for MyTable.Thickness in my example :)
 
Actually, how do I make a visible column vs bound column in Access
2000. For a dropdown there's column count and bound column. I don't
see how to make visible column different than bound column and only
have one column displayed.
 
SirPoonga said:
Actually, how do I make a visible column vs bound column in Access
2000. For a dropdown there's column count and bound column. I don't
see how to make visible column different than bound column and only
have one column displayed.
Click to expand...

Set ColumnCount to 2, BoundColumn to 1, and set the ColumnWidths
property so that the width of the first column is zero; e.g.,

ColumnWidths: 0;1"
 
It tried that too. I'm actually having a problem with displaying the
data. To make sure everything was correct I left 2 columns visible.
However, Thickness column (not display) was showing nothing except a
couple of character boxes for some values while the Display column was
showing correctly.

I did get it to work by douing a 1 column with this statement. I
needed to put the () around the second part otherwise it complained
about the order by.

SELECT '*' AS Display, null as Thickness
FROM MyTable
UNION
(SELECT DISTINCT MyTable.Thickness AS Display,MyTable.Thickness
FROM [Edge Base Cutting]
ORDER BY [Edge Base Cutting].Thickness)

This orders everything properly. I just have to handle an '*' instead
of a null. But it works.
 
Scratch that. It still isn't ordering as if it were a number. It is
still ordering it as if it was text. Hmmmm....
 
Errr, forgot to change all the table names for my example. There's a
huge where statement to go along with this, that's why I am turning my
statement into a small example. MyTable = Edge Base Cutting

Anyway, I figured it out, not sure how to resolve it.
I took off the UNION with the '*' to see if the query would show up
correctly using the Design View. It didn't show up correctly. It was
sorting using the Display field, even though I had ORDER By
MyTable.Thickness. However, I changed the Ascending to the right
column in Design view and it seems to work. But as soon as I
reintroduce the union it sorts via text style sorting again.
 
SirPoonga said:
Errr, forgot to change all the table names for my example. There's a
huge where statement to go along with this, that's why I am turning my
statement into a small example. MyTable = Edge Base Cutting

Anyway, I figured it out, not sure how to resolve it.
I took off the UNION with the '*' to see if the query would show up
correctly using the Design View. It didn't show up correctly. It was
sorting using the Display field, even though I had ORDER By
MyTable.Thickness. However, I changed the Ascending to the right
column in Design view and it seems to work. But as soon as I
reintroduce the union it sorts via text style sorting again.
Click to expand...

You're not using anything at all like the SQL statement I recommended.
Try this exactly, and if it doesn't work, report what is wrong:

SELECT
Thickness,
CStr(Thickness) As Display
FROM [Edge Base Cutting]
UNION
SELECT Null As Thickness, '*' as Display
FROM [Edge Base Cutting]
ORDER BY Thickness;

Note that this puts the Thickness column as the first column and the
Display column second, so you must set the combo's BoundColumn property
to 1.
 
I did. I said it didn't work and for the thickness column it was
showing blanks, for some values that box ascii character. What I
listed is what I thought worked. So, so far nothing has worked.

With what you just typed I get an invalid use of null. I figured it
why it is saying htat. I have null thickness values in the table. So
I added a WHERE Thickness Is Not Null to take the problems ones out of
the picture for now.
Again, I get for the Thickness column mostly blanks except for the
occational ascii box (like there was a return character there), but the
display is showing values, ordering as if it were text.

Using the query builder to see what results I get, here's what I got
http://img3.exs.cx/my.php?loc=img3&image=query7zm.png
 
Another note: If I take off the union and leave
SELECT
Thickness,
CStr(Thickness) As Display
FROM [Edge Base Cutting]
ORDER BY Thickness;

That displayed just fine. It's something to do with the union.
Is there a better way to add a "wildcard" item in a dropdown?
 
Another note: If I replace null with 0 I got values for the Thickness
column and it ordered as if everything was a number. So I decided to
try and replace null with ''. This ordered as if it were text which
makes sense as itis doing what my first variation of the statement was.

So, It hink, to solve my problem I need to make the wildcard value be
something that will not exist, luckily in this case there are no
negatives. I could make it equal to -1. Then in the code that applies
this custom filter if it is a negative handle it as a wildcard.
 
SirPoonga said:
Another note: If I replace null with 0 I got values for the Thickness
column and it ordered as if everything was a number. So I decided to
try and replace null with ''. This ordered as if it were text which
makes sense as itis doing what my first variation of the statement
was.

So, It hink, to solve my problem I need to make the wildcard value be
something that will not exist, luckily in this case there are no
negatives. I could make it equal to -1. Then in the code that
applies this custom filter if it is a negative handle it as a
wildcard.
Click to expand...

That's reasonable, and you can probably force the interpretation of the
field as a Number at the same time. Your SQL would then look like this,
I expect:

SELECT -1.0 As Thickness, '*' as Display
FROM [Edge Base Cutting]
UNION
SELECT
Thickness,
CStr(Thickness) As Display
FROM [Edge Base Cutting]
WHERE Thickness Is Not Null
ORDER BY Thickness;
 
yep, that worked perfectly. When it unions, because it unions with a
text character, it must sort everything as text then.
 
SirPoonga said:
yep, that worked perfectly. When it unions, because it unions with a
text character, it must sort everything as text then.
Click to expand...

Great! At last! The problem is that when you union dissimilar data, it
has to pick an appropriate field type, and under various circumstances
it guesses wrong. This box characters you were seeing were the result
of trying to interpret your floating-point number fields as if they were
text fields.
 
Right. It'd be nice if you put parens around a select statement with
an order by in a union that it would just order the part that select
creates and appends onto the other selects in the union. Instead of
ordering everything. Or is there a way to do that? From what I tried
I didn't see a way.
 
SirPoonga said:
Right. It'd be nice if you put parens around a select statement with
an order by in a union that it would just order the part that select
creates and appends onto the other selects in the union. Instead of
ordering everything. Or is there a way to do that? From what I tried
I didn't see a way.
Click to expand...

I think internal ORDER BY clauses are ignored. Certainly they will be
if you use UNION instead of UNION ALL, thus eliminating duplicate
records, because the records have to be sorted to ensure that duplicates
are identified.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Order By SQL 2
I need creating a query to show the Cost results from the general 1
Data Mismatch in Excel 2010 4
SQL "IF" Question 5
Listbox Rowsource 11
Query Inside an SQL statement 3
A Real Stumper 3
SQL Server connection 17

Back
Top