Solved

order by in vb code

Posted on 2004-10-19
7
262 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

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.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

623 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