Solved

order by in vb code

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now