SELECT DISTINCT LEFT(CAST(日期列 AS varchar(10)), 4) FROM 表
如果是ACCESS SELECT YEAR(日期) FROM 表1如果是SQL SERVER SELECT SUBSTRING(日期,1,4) FROM 表1
select year(列) from 表1
select distinct convert(char(4),日期,111) from table这样就可以了吧。Access里边应该也可以这样用。
SELECT DISTINCT YEAR(日期) FROM table
Year(日期字段)Datepart(yy,日期字段)
谢谢各位朋友。你们提供的方法我都试了,为什么查讯不出结果?请看代码: Dim Cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Form_Load() Dim Source As String Dim ActiveConnection As String Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb" Cn.Open On Error Resume Next ' Source = "select distinct 录入时间 from 日记" ' Source = "select distinct DatePart('yyyy', 录入时间) from 日记" ' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记" ' Source = "select distinct SUBSTRING(录入时间,1,4)from 日记" Source = "select distinct year(录入时间) from 日记" rs.Open Source, Cn, 3, 3 rs.MoveFirst Do Until rs.EOF ' List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间)) rs.MoveNext Loop rs.Close End Sub
我試過了, select distinct year(fieldname) from tablename 沒有錯。Len(DatePart("yyyy", rs!录入时间)) 為什麼會等於1呢?錯誤可能不在sql語句喔!!!
代码摘录有错,应为:List1.AddItem DatePart("yyyy", rs!录入时间)现在问题是查讯后,提示“在对应所需名称或序数的集合中,未找到项目”,但表中明明有项目的,用“select distinct 录入时间 from 日记”可以证明。哪位朋友用select distinct year(fieldname) from tablename 查讯通过测试,请贴出代码以便比较一下,谢谢!
If Option5.Value = True Then listbox.FixedRows = 1 MousePointer = vbHourglass Dim rs5 As New ADODB.Recordset Dim rs6 As New ADODB.Recordset Dim rscount As New ADODB.Recordset Dim countrscount As Integer Dim countw As Integer Dim compyear As Integer str1 = "select year(ctime) from synthesisanalyse group by year(ctime)" rscount.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText countrscount = 0 Do While Not rscount.EOF countrscount = countrscount + 1 rscount.MoveNext Loop
str1 = "select usertype,typename from waterpriceinfo " rs5.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText On Error GoTo errorbox countw = 0 Do While Not rs5.EOF countw = 1 + countw rs5.MoveNext Loop rs5.MoveFirst Dim fld As ADODB.Field
listbox.Text = "年份" Do While Not rs5.EOF listbox.Col = listbox.Col + 1 listbox.Row = 0 listbox.Text = rs5.Fields(1) rs5.MoveNext Loop rs5.MoveFirst
str2 = "select year(ctime),typename,sum(watervalue) from waterpriceinfo,synthesisanalyse " str2 = str2 + " where waterpriceinfo.usertype=synthesisanalyse.usertype group by typename ,year(ctime) " rs6.Open str2, cn, adOpenKeyset, adLockOptimistic, adCmdText listbox.Row = 0
If Not rs6.EOF Then '第一行的年直接写 listbox.Row = listbox.Row + 1 listbox.Col = 0 listbox.Text = rs6.Fields(0) Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF rs5.MoveNext listbox.Col = listbox.Col + 1 Loop rs5.MoveFirst listbox.Col = listbox.Col + 1 '还须后移 If IsNull(rs6.Fields(2)) Then listbox.Text = "" Else listbox.Text = rs6.Fields(2) End If compyear = rs6.Fields(0) rs6.MoveNext End If Do While Not rs6.EOF '第一行后的年都要比较 If rs6.Fields(0) <> compyear Then compyear = rs6.Fields(0) listbox.Row = listbox.Row + 1 listbox.Col = 0 listbox.Text = rs6.Fields(0) End If 'If rs6.Fields(1) <> rs5.Fields(1) Then Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF rs5.MoveNext listbox.Col = listbox.Col + 1 Loop ' rs5.MoveFirst listbox.Col = listbox.Col + 1 If IsNull(rs6.Fields(2)) Then listbox.Text = "" Else listbox.Text = rs6.Fields(2) End If rs5.MoveFirst listbox.Col = 0 ' Else ' listbox.Text = rs6.Fields(2) ' rs5.MoveFirst ' End If rs6.MoveNext Loop
MousePointer = vbDefault If listbox.Rows = 1 Then listbox.Rows = 2 End If
End If 这是我在学校时编的,通过运行。 看是否用得着。
Dim Cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Form_Load() Dim Source As String Dim ActiveConnection As String Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb" Cn.Open On Error Resume Next ' Source = "select distinct 录入时间 from 日记" ' Source = "select distinct DatePart('yyyy', 录入时间) from 日记" ' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记" ' Source = "select distinct SUBSTRING(录入时间,1,4)from 日记" Source = "select distinct year(录入时间) AS 录入时间 from 日记" rs.Open Source, Cn, 3, 3 rs.MoveFirst Do Until rs.EOF ' List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间)) rs.MoveNext Loop rs.Close End Sub
如果还不行,建议你注释掉On Error Resume Next以查看错误原因
Option Explicit Public conn As New ADODB.Connection Public rs1 As New ADODB.RecordsetPrivate Sub Form_Load() Dim connect_str As String Dim i As Integer connect_str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=newman;password=newman;" connect_str = connect_str & "Initial Catalog=Pubs;Data Source=192.168.11.254;Connect Timeout=30;Application Name=ListenEZ" conn.open connect_str
If rs1.state = 1 Then rs1.Close rs1.open "select distinct year(ord_date) as years from sales", conn, 3, 1
For i = 1 To rs1.RecordCount List1.AddItem rs1("years") Next i End Sub剛寫的一段代碼,已以通過了運行,你可以將數據庫連接改一下就行了。 希望對你有幫助。
SELECT YEAR(日期) FROM 表1如果是SQL SERVER
SELECT SUBSTRING(日期,1,4) FROM 表1
FROM table
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Form_Load()
Dim Source As String
Dim ActiveConnection As String
Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb"
Cn.Open
On Error Resume Next
' Source = "select distinct 录入时间 from 日记"
' Source = "select distinct DatePart('yyyy', 录入时间) from 日记"
' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记"
' Source = "select distinct SUBSTRING(录入时间,1,4)from 日记"
Source = "select distinct year(录入时间) from 日记"
rs.Open Source, Cn, 3, 3
rs.MoveFirst
Do Until rs.EOF '
List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间))
rs.MoveNext
Loop
rs.Close
End Sub
listbox.FixedRows = 1
MousePointer = vbHourglass
Dim rs5 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rscount As New ADODB.Recordset
Dim countrscount As Integer
Dim countw As Integer
Dim compyear As Integer
str1 = "select year(ctime) from synthesisanalyse group by year(ctime)"
rscount.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
countrscount = 0
Do While Not rscount.EOF
countrscount = countrscount + 1
rscount.MoveNext
Loop
str1 = "select usertype,typename from waterpriceinfo "
rs5.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
On Error GoTo errorbox
countw = 0
Do While Not rs5.EOF
countw = 1 + countw
rs5.MoveNext
Loop
rs5.MoveFirst
Dim fld As ADODB.Field
' Setup the hflxResults
'listbox.Clear
listbox.FixedCols = 0
listbox.FixedRows = 1
listbox.Cols = countw + 1 '加1是年份列
listbox.Rows = countrscount + 1
listbox.Row = 0
listbox.Col = 0
listbox.Text = "年份"
Do While Not rs5.EOF
listbox.Col = listbox.Col + 1
listbox.Row = 0
listbox.Text = rs5.Fields(1)
rs5.MoveNext
Loop
rs5.MoveFirst
str2 = "select year(ctime),typename,sum(watervalue) from waterpriceinfo,synthesisanalyse "
str2 = str2 + " where waterpriceinfo.usertype=synthesisanalyse.usertype group by typename ,year(ctime) "
rs6.Open str2, cn, adOpenKeyset, adLockOptimistic, adCmdText
listbox.Row = 0
If Not rs6.EOF Then '第一行的年直接写
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
rs5.MoveFirst
listbox.Col = listbox.Col + 1 '还须后移
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
compyear = rs6.Fields(0)
rs6.MoveNext
End If
Do While Not rs6.EOF '第一行后的年都要比较
If rs6.Fields(0) <> compyear Then
compyear = rs6.Fields(0)
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
End If
'If rs6.Fields(1) <> rs5.Fields(1) Then
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
' rs5.MoveFirst
listbox.Col = listbox.Col + 1
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
rs5.MoveFirst
listbox.Col = 0
' Else
' listbox.Text = rs6.Fields(2)
' rs5.MoveFirst
' End If
rs6.MoveNext
Loop
MousePointer = vbDefault
If listbox.Rows = 1 Then
listbox.Rows = 2
End If
End If
这是我在学校时编的,通过运行。
看是否用得着。
Dim rs As New ADODB.RecordsetPrivate Sub Form_Load()
Dim Source As String
Dim ActiveConnection As String
Cn.ConnectionString = "uid=admin;pwd=1234;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\日记.mdb"
Cn.Open
On Error Resume Next
' Source = "select distinct 录入时间 from 日记"
' Source = "select distinct DatePart('yyyy', 录入时间) from 日记"
' Source = "select distinct LEFT(CAST(录入时间 AS varchar(10)), 4) from 日记"
' Source = "select distinct SUBSTRING(录入时间,1,4)from 日记"
Source = "select distinct year(录入时间) AS 录入时间 from 日记"
rs.Open Source, Cn, 3, 3
rs.MoveFirst
Do Until rs.EOF '
List1.AddItem IIf(Len(DatePart("yyyy", rs!录入时间)) = 1, 0 & DatePart("yyyy", rs!录入时间), DatePart("yyyy", rs!录入时间))
rs.MoveNext
Loop
rs.Close
End Sub
Public conn As New ADODB.Connection
Public rs1 As New ADODB.RecordsetPrivate Sub Form_Load()
Dim connect_str As String
Dim i As Integer
connect_str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=newman;password=newman;"
connect_str = connect_str & "Initial Catalog=Pubs;Data Source=192.168.11.254;Connect Timeout=30;Application Name=ListenEZ"
conn.open connect_str
If rs1.state = 1 Then rs1.Close
rs1.open "select distinct year(ord_date) as years from sales", conn, 3, 1
For i = 1 To rs1.RecordCount
List1.AddItem rs1("years")
Next i
End Sub剛寫的一段代碼,已以通過了運行,你可以將數據庫連接改一下就行了。
希望對你有幫助。