1

I'm trying to get data that are equals to and between 2 dates that i type into my textbox! The column of PO_Date is of DateTime. It works perfectly when i hard code the dates into the select statement, for example PO_Date >= #12/6/2013 3:54:15 PM# AND PO_Date <= #1/1/2015 3:54:15 PM# inclusive of the DropDownList2. The error only comes once i try to implement the txtStartDate.Text and txtEndDate.Text! Help is very much appreciated!

oRs.Open("SELECT PO_Date, PO.Vendor FROM PO 
WHERE PO_Date >= '#" & TxtStartDate.Text & "00:00:00 AM""#' AND
PO_Date <= '#" & TxtEndDate.Text & "00:00:00 AM""#' 
AND Vendor Like '%" & DropDownList2.SelectedValue & "%'", oCnn)
Melissa S. Christ
  • 49
  • 1
  • 5
  • Not sure this is your problem but I would think you need a space before the `00:00:00 AM` Like `TxtEndDate.Text & " 00:00:00 AM""#` – Fred Jun 17 '15 at 06:29
  • @Fred There wasn't a difference unfortunately – Melissa S. Christ Jun 17 '15 at 06:37
  • I'm typing 1/1/2013 and 1/1/2015 by the way! – Melissa S. Christ Jun 17 '15 at 06:40
  • Try assigning your query to a variable then looking at the value of that variable. `strSQL = "SELECT PO_Date, PO.Vendor FROM PO WHERE PO_Date >= #" & TxtStartDate.Text & " 00:00:00 AM# AND PO_Date <= #" & TxtEndDate.Text & " 00:00:00 AM# AND Vendor Like '%" & DropDownList2.SelectedValue & "%'"` you may see where the query is incorrect – Fred Jun 17 '15 at 06:45
  • @Fred The current problem has already been solved but this was literally my next problem haha! I've tried to store a query into a string `Dim query As String = "SELECT ....."` but when i try to output whats in the variable "query", it just shows the entire select statement! – Melissa S. Christ Jun 17 '15 at 06:54
  • That would be correct. What it will do is show you the select statement with the values from the text boxes in place. You can then use `oRs.Open(query, oCnn)`. It makes debugging easier. You can also copy the entire select statement and run it against the database to see if it is correct and what it actually returns. – Fred Jun 17 '15 at 07:00
  • FYI the error message is a catchall and can usually be read as "syntax error." – rheitzman Jun 17 '15 at 14:42

1 Answers1

1

May be you have typo, but you can check:

oRs.Open("SELECT PO_Date, PO.Vendor FROM PO 
WHERE PO_Date >= #" & TxtStartDate.Text & " 00:00:00 AM# AND
PO_Date <= #" & TxtEndDate.Text & " 00:00:00 AM# 
AND Vendor Like '%" & DropDownList2.SelectedValue & "%'", oCnn)
  • AM""#' => AM#
  • "00:00:00 => " 00:00:00
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75