Member Avatar for chanlichin

hi,i am new to vb 8 and i am now facing the problem to write sql statement.

i want to retrieved data from MS Access by select the date using date time picker. so my problem is how to write the sql statement.

My code is as below:
sql = "SELECT * FROM summary WHERE line = '" & cbLine.Text & "' AND date = '" & dtp.Text & "'AND plants = " & cbPlant.Text & ""

  • 2 Contributors
  • 4 Replies
  • 81 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by chanlichin
Member Avatar for DB_err

Are all of these columns text fields? If so you seem to be missing the single quotes for the final field. I think this would work:

sql = "SELECT * FROM summary WHERE line = '" & cbLine.Text & "' AND date = '" & dtp.Text & "' AND plants = '" & cbPlant.Text & "'"
Member Avatar for chanlichin

Yes,Thanks DB.
I would like to ask if my field 'date', i set the the data type for it is Date/Time, Format is short date, so what should i change the name 'Text" in sql query such as coding below:
date = '" & dtp.Text & "'
i am using date time picker

Member Avatar for DB_err

You should use the Value property, it will return a date value.

I would try something like this;

"date = " & dtp.Value & "

You may need to work a bit with the date formatting to get it to work with your values in the table.

Member Avatar for chanlichin

Hi,Thanks DB.
I just change to value and add the symbol #, then it is work.

sql2 = "SELECT * FROM summary WHERE line = '" & cbLine.Text & "'AND dates = #" & dtp.Value & " # AND plants = '" & cbPlant.Text & "'"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.