Change a DBRW custom query

[LScal]

My question is two-fold....
Using FP2003 I have a results page that displays data based on whatever
criteria the user chooses using search buttons and drop down lists. The
query works OK and looks like this:


SELECT * FROM tblName WHERE (Column LIKE '%::blah::%' AND Column LIKE
'%::blah::%' AND Column LIKE '%::blah::%' AND Column <> 'administrator')



I want to change the custom query in the DBRW to sort by a specific field,
so it would look like this:



SELECT * FROM tblName WHERE (Column LIKE '%::blah::%' AND Column LIKE
'%::blah::%' AND Column LIKE '%::blah::%' AND Column <> 'administrator'
ORDER BY Column DESC)



Is there any way to do this without the Results page rebuilding itself and
destroying my layout?



Also, on a more complicated level, one of my db columns contains a path to
pictures. If there is no unique picture to display there's a default image
whose path is automatically entered in a column "/images/nopic.jpg". If
there is a unique picture the field is populated based on another column
"/images/membername.jpg". I want to sort the db results like above, but also
sort the results so the records with unique images are displayed first. The
image path column is different than the ORDER BY column in my query above.



Can I do a multiple column sort with specifics (maybe to include something
like this Column <> '/images/nopic.jpg') and does anyone know where I can
possibly find a sample query for it?



This is a lot to ask, I know, but I would appreciate any insight I can get.
All I know about querying is basically nothing. Thanks in advance.

 

[Kathleen Anderson [MVP - FrontPage]]

Inline.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

My question is two-fold....
Using FP2003 I have a results page that displays data based on whatever
criteria the user chooses using search buttons and drop down lists. The
query works OK and looks like this:


SELECT * FROM tblName WHERE (Column LIKE '%::blah::%' AND Column LIKE
'%::blah::%' AND Column LIKE '%::blah::%' AND Column <> 'administrator')



I want to change the custom query in the DBRW to sort by a specific field,
so it would look like this:



SELECT * FROM tblName WHERE (Column LIKE '%::blah::%' AND Column LIKE
'%::blah::%' AND Column LIKE '%::blah::%' AND Column <> 'administrator'
ORDER BY Column DESC)



Is there any way to do this without the Results page rebuilding itself and
destroying my layout?

I think easiest way to do this is to open your page, switch to Code view,
find the SQL in the gray colored code, add the "ORDER BY Column DESC to the
query and save your change while you're still in Code view. Do not make the
chnage to the maroon code - when you save the page the chnage you made to
the gray code will replicate to the maroon code.

Also, on a more complicated level, one of my db columns contains a path to
pictures. If there is no unique picture to display there's a default image
whose path is automatically entered in a column "/images/nopic.jpg". If
there is a unique picture the field is populated based on another column
"/images/membername.jpg". I want to sort the db results like above, but
also
sort the results so the records with unique images are displayed first.
The
image path column is different than the ORDER BY column in my query above.

Off the top of my head, I would try changing the name of the nopic.jpg to
zzzzz.jpg and order by that column desc.

Can I do a multiple column sort with specifics (maybe to include something
like this Column <> '/images/nopic.jpg') and does anyone know where I can
possibly find a sample query for it?

I think ORDER BY imagefield DESC, column DESC might work.

 

[LScal]

Kathleen,
Excellent! I edited the query in code view and it worked as expected.
I did not try the second, but changing the default image name to zzzz.jpg
and sorting against that is brilliantly simple. What a great idea!

Thanks very much.
Lisa

 

[Kathleen Anderson [MVP - FrontPage]]

Hi Lisa:
Thanks for letting me know - It was early; I'd only had one cup of coffee
and I wasn't thinking all that clearly

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx