VBA sql conversion failed when converting the varchar value to data type int in FILTER BETWEEN dates

[geloader0]

Hi Good day, I have a form with data and search box. I want to filter dates between 2 fields/textbox From and To. But this error appear as shown in the image below.
This is my SQL query . Thank you for the help.

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where [DATE BILLED] BETWEEN " & dtpFrom.Text & " AND " & dtpTo.Text & "  " & _
  " AND [SYS Payment Rcvd]='' order by [Row Number] DESC "

 

[geloader0]

But when I use LIKE query it works using this line

[SYS Payment Rcvd]=''

. But in the dates filter using between the 2 dates in the textbox. Results not found

 

[FormR]

Here's one more effort then - I'm running out of ideas though.

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where CAST([DATE BILLED] AS DATE) BETWEEN '" & Format(CDate(dtpFrom.Text), "yyyy-mm-dd") & "' AND '" & Format(CDate(dtpTo.Text), "yyyy-mm-dd") & "'  " & _
  " AND ISNULL([SYS Payment Rcvd],'') = '' order by [Row Number] DESC "

 

[geloader0]

Here's one more effort then - I'm running out of ideas though.

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where CAST([DATE BILLED] AS DATE) BETWEEN '" & Format(CDate(dtpFrom.Text), "yyyy-mm-dd") & "' AND '" & Format(CDate(dtpTo.Text), "yyyy-mm-dd") & "'  " & _
  " AND ISNULL([SYS Payment Rcvd],'') = '' order by [Row Number] DESC "

Now, This error appears.

 

[FormR]

This whole problem is nothing to do with VBA or Excel and it's difficult to help without knowing anything about your database/table set-up/data.

You really need to get a working SQL statement from someone who knows SQL and knows your database - then me (or someone else here) can then help you to create it in VBA.

Last try..

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where str_to_date([DATE BILLED] AS DATE, '%m/%d/%y') BETWEEN " & Format(CDate(dtpFrom.Text), "yyyy-mm-dd") & "' AND '" & Format(CDate(dtpTo.Text), "yyyy-mm-dd") & "'  " & _
  " AND ISNULL([SYS Payment Rcvd],'') = '' order by [Row Number] DESC "

 

[geloader0]

This whole problem is nothing to do with VBA or Excel and it's difficult to help without knowing anything about your database/table set-up/data.

You really need to get a working SQL statement from someone who knows SQL and knows your database - then me (or someone else here) can then help you to create it in VBA.

Last try..

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where str_to_date([DATE BILLED] AS DATE, '%m/%d/%y') BETWEEN " & Format(CDate(dtpFrom.Text), "yyyy-mm-dd") & "' AND '" & Format(CDate(dtpTo.Text), "yyyy-mm-dd") & "'  " & _
  " AND ISNULL([SYS Payment Rcvd],'') = '' order by [Row Number] DESC "

Anyway, The format of DATE BILLED in the table is VARCHAR not a date.

 

[FormR]

The format of DATE BILLED in the table is VARCHAR not a date.

Yes, so maybe you've got entries in there that can't be converted to a date! The last post# should ignore those - but I don't have access to a mysql database to be able to do any testing.

 

[geloader0]

Yes, so maybe you've got entries in there that can't be converted to a date! The last post# should ignore those - but I don't have access to a mysql database to be able to do any testing.

Anyway in your SQL statement gives me this error.

 

[FormR]

My mistake (like I say, I have no way of testing what I'm posting). .

sql = "select [Row Number],[ITEM],[NAME],[SO #],[DATE BILLED] from tblData  " & _
  " where str_to_date([DATE BILLED], '%m/%d/%y') BETWEEN " & Format(CDate(dtpFrom.Text), "yyyy-mm-dd") & "' AND '" & Format(CDate(dtpTo.Text), "yyyy-mm-dd") & "'  " & _
  " AND ISNULL([SYS Payment Rcvd],'') = '' order by [Row Number] DESC "

 

[geloader0]

Yes, so maybe you've got entries in there that can't be converted to a date! The last post# should ignore those - but I don't have access to a mysql database to be able to do any testing.

Yeah. I thought the CAST thing is the solution in order to convert it to a date. But nothing works. Maybe you're right. That there is a record that cannot be converted into a date. there is 58,000 rows and it is hard to find that record.

 

[geloader0]

I think the DATE BILLED field in the database is the problem because when I tried to search other DATE field that has a DATE format not a VARCHAR... The search works. So, in order to achieve problem this I need to convert DATE BILLED field to a DATE from the SQL statement