select distinct mid

Question

select distinct mid

snow_rose_87

SSC Veteran

Points: 245
More actions
May 11, 2008 at 9:05 pm

#207262

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

snow_rose_87 SSC Veteran Points: 245 More actions · Answer 2

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...

Ramkumar.K Default port Points: 1405 More actions · Answer 3

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....
######################

snow_rose_87 SSC Veteran Points: 245 More actions · Answer 4

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?

Ramkumar.K Default port Points: 1405 More actions · Answer 5

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....
######################

DAVNovak Hall of Fame Points: 3120 More actions · Answer 6

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

slava-415059 Grasshopper Points: 22 More actions · Answer 7

Try using the following query:

SELECT DISTINCT (mid(no_reg,3,2))

FROM students

It avoids using subqueries, and should work in MS Access.