Solved

order by in vb code

Posted on 2004-10-19
7
255 Views
Last Modified: 2010-08-05
i have code in vb like this(table is in sql 2000 )


sql = ""
sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='" _
    & " order by p.Field1 DESC,p.Field2 "


if i comment order by i get the recordset but i want it in descending order by field1. why do i get an empty recordset? please can someone help me answer this problem?
thanks
0
Comment
Question by:phisa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Accepted Solution

by:
domj earned 50 total points
ID: 12353331
this string returns:

Select p.field1,pfield2 from table1 as p, table2 as te where te.id=p.id and p.id=' order by p.field1 desc,p.field2

Looks like you're missing something after and p.id='. I would guess that this is where the issue is.

Dom
0
 

Assisted Solution

by:pete1ryan
pete1ryan earned 50 total points
ID: 12353413
phisa, further to what Dom said, it looks to me like you've tried to comment within your quote marks (i'll space it out)

& " and p.id =          '            " _
& " order by p.Field1 DESC,p.Field2 "

Instead of commenting the order by clause, that will just include a single quote in your string (as mentioned by dom).

Not exactly sure what the end result you are looking for is but if you are trying to construct a SQL query based on an ID from a VB form (let's say the variable is called 'myID') then u need to do this.

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id = " & myID _
    & " order by p.Field1 DESC,p.Field2 "

If you want to comment out the order by as you have the code written, you would need this:

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id =" '_
    '& " order by p.Field1 DESC,p.Field2 "


If this hasn't helped, let me know exactly what you're trying to do.
0
 

Author Comment

by:phisa
ID: 12353641
i'm writing same data from sql to *.txt file in a specific order.


my first rs looks like

sql1 = "select * from table2 " _
& " Order by field4,ID desc"

and this one is working like this

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='"   ''( p.id = ' ) referes to id from sql1
 

BUT i want order by field1 desc and when i write like this, i get an empty recordset. how should the script look like? i also tried order by 1 desc, 2.
 sql = "Select p.Field1,p.Field2  "
 sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='" _
    & " order by p.Field1 DESC,p.Field2"


0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12353760
the sql should look like this:

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " order by p.Field1 DESC,p.Field2 "

AW
0
 

Author Comment

by:phisa
ID: 12354356
I fixed the problem in the line
cmdtest.CommandText = sql & rs.sql1.Fields(0) & "'"
add & " order by p.field1 DESC "
cmdtest.CommandText = sql & rs.sql1.Fields(0) & "'" & " order by p.field1 DESC "

sql is still the same
sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='"

thank you very much for your help.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question