May 11, 2008 at 9:05 pm
hi guys,i need your help. can you give me abot syntax of "select distict mid/left/right"?
i wanna select 2 characters from this record in a table to a combo box
10073010002
10072010012
10063020001
10083010001
how can i put "07","06","08" into combo box?thx b4
May 11, 2008 at 9:45 pm
snow_rose_87 (5/11/2008)
hi guys,i need your help. can you give me abot syntax of "select distict mid/left/right"?i wanna select 2 characters from this record in a table to a combo box
10073010002
10072010012
10063020001
10083010001
how can i put "07","06","08" into combo box?thx b4
Lookup SUBSTRING in BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a19c808f-aaf9-4a69-af59-b1a5fc3e5c4c.htm
SUBSTRING( expression, start, length )
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2008 at 9:59 pm
i'm sorry mr jeff...is it the syntax of msql?but i wanna make it in access from vb 6.0 code?can it be?please help me...
May 11, 2008 at 10:17 pm
Hi Rose..
You try this...
select distinct * from (
SELECT mid(reg_year,3,2)
FROM tbl_dept) a;
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
May 11, 2008 at 10:40 pm
i had try your solution mr.ramkumar...but i get message "syntax error in from clause"
this is my code:
Dim rsx As Recordset
Dim reg_year As String
Set rsx = dbStudent.OpenRecordset("select distinct * from (select mid(no_reg,3,2) from students) as noregselection")
reg_year = noregselection
Combobox1.Clear
If Not rsx.BOF Then
rsx.MoveFirst
End If
While Not rsx.EOF
Combobox1.AddItem reg_year
rsx.MoveNext
Wend
any wrong with it?
May 12, 2008 at 8:32 am
Hi Rose..
I have given the sample code for your reference..
That is in Vb.net 2005..
Dim Con As New OleDbConnection(My.Settings.AccConStr)
Dim DA As New OleDbDataAdapter("select distinct * from (SELECT mid(reg_year,3,2) FROM tbl_dept) as noregselection", Con)
Dim dt As New DataTable
DA.Fill(dt)
For i As Integer = 0 To dt.Rows.Count - 1
MsgBox(dt.Rows(i).Item(0).ToString)
I checked the code.. thats working fine for me..
i think u have the bug in your code in below region..
While Not rsx.EOF
Combobox1.AddItem reg_year
rsx.MoveNext
Wend
you need to retrieve the data from the record set (rsx).. But you are trying to get it from reg_year, which you have declared as string..
Thanks & Regards
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
May 12, 2008 at 1:17 pm
You can not do a derived table in MS Access. The derived table is the select statement in the '()' in the FROM clause. You will have to create a query in MS Access that is this SQL statement and then call use the query in its place.
Dave Novak
May 13, 2008 at 3:15 pm
Try using the following query:
SELECT DISTINCT (mid(no_reg,3,2))
FROM students
It avoids using subqueries, and should work in MS Access.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply