[chanlichin]

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.

con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x

[piman314]

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?

[PsychoCoder]

If you're using SQL Server check out DATEPART (or DATEPART for Access)

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

[chanlichin]

piman314, on 23 Aug, 2009 - 08:31 PM, said:

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?

piman314, on 23 Aug, 2009 - 08:31 PM, said:

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?

The format is mm/dd/yyyy
Thanks for reply.

This post has been edited by chanlichin: 23 August 2009 - 10:38 PM

[PsychoCoder]

Well what you tried isnt even what I showed you. What I showed was

SQL Server

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

Or

Microsoft Access

sql = "SELECT DATEPART("yyyy",dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

[chanlichin]

PsychoCoder, on 23 Aug, 2009 - 08:40 PM, said:

If you're using SQL Server check out DATEPART (or DATEPART for Access)

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

i am using database MS Access. So i just change to

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

But msg come out "No value given for one or more required parameters".
Izzit is the problem occur below.

For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x

[chanlichin]

PsychoCoder, on 23 Aug, 2009 - 09:37 PM, said:

Well what you tried isnt even what I showed you. What I showed was

SQL Server

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

Or

Microsoft Access

sql = "SELECT DATEPART("yyyy",dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

I just add "" between yyyy but come out error "End of statement expected".
May i know what happen?Thanks

[DataPriest]

Try using:

sql = "SELECT DATEPART('yyyy', dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

[chanlichin]

DataPriest, on 24 Aug, 2009 - 01:45 AM, said:

Try using:

sql = "SELECT DATEPART('yyyy', dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

The combo box still can not display the year,really dun know what to do already....

[DataPriest]

chanlichin, on 23 Aug, 2009 - 06:57 PM, said:

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.

con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x

Try using:

		For x = 0 To maxrow - 1

			cbYear.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))
			cbYear1.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))

		Next x

[chanlichin]

DataPriest, on 24 Aug, 2009 - 04:40 AM, said:

chanlichin, on 23 Aug, 2009 - 06:57 PM, said:

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.

con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x

Try using:

		For x = 0 To maxrow - 1

			cbYear.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))
			cbYear1.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))

		Next x

Hi,DataPriest, it has the error "
Class 'System.Windows.Forms.Label' cannot be indexed because it has no default property." Year was higlighted

[motcom]

hi,

since you are using a for loop,

you could do this (if your field is not dbnull)

 For x = 0 To maxrow - 1
				cbYear.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates")).year)
				cbYear1.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates").year)
			Next x

[chanlichin]

motcom, on 24 Aug, 2009 - 05:15 AM, said:

hi,

since you are using a for loop,

you could do this (if your field is not dbnull)

 For x = 0 To maxrow - 1
				cbYear.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates")).year)
				cbYear1.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates").year)
			Next x

Yes, i can display the year already. But how should i do if i dun want the year be repeated?

[motcom]

Try this for your sql statement

sql = "SELECT DISTINCT(DATEPART("yyyy",dates)) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'" 

[chanlichin]

motcom, on 24 Aug, 2009 - 06:51 AM, said:

Try this for your sql statement

sql = "SELECT DISTINCT(DATEPART("yyyy",dates)) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'" 

I try it already. But have the error "End of statement expected". What is the problem actually?Thanks...