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 "

 

[FormR]

Try like this:

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

 

[geloader0]

Try like this:

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

Still there is no data found.

 

[FormR]

If you wrote the query outside of VBA - what would it look like? Can you post it here?

 

[geloader0]

If you wrote the query outside of VBA - what would it look like? Can you post it here?

Sorry, I don't get your point.

 

[FormR]

Your trying to dynamically create an SQL statement using VBA - what should that final SQL statement look like?

 

[geloader0]

Your trying to dynamically create an SQL statement using VBA - what should that final SQL statement look like?

This is my code including the SQL statement that you give me.

 

[FormR]

I think you are missing my point - or perhaps you don't know the answer.

So, back to guessing..

Is [SYS Payment Rcvd] really blank - or does it actually contain NULL's?

 

[geloader0]

I think you are missing my point - or perhaps you don't know the answer.

So, back to guessing..

Is [SYS Payment Rcvd] really blank - or does it actually contain NULL's?

Sorry. Yes it is, blank or null. The purpose of this line

[SYS Payment Rcvd]=''

is to search in the database that no payment received yet.

 

[FormR]

blank or null

They are not quite the same thing though.

What happens with:

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

 

[geloader0]

They are not quite the same thing though.

What happens with:

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

Still no results found. I tried different formats in the textbox just to match but still not results found.