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]

Hi, what exactly do you have in the "dtpFrom" and "dtpTo" text boxes?

What type of database are you trying to query?

If you output the contents of the sql variable to the immediate window with ? sql does it look as expected?

 

[geloader0]

Hi, what exactly do you have in the "dtpFrom" and "dtpTo" text boxes?

What type of database are you trying to query?

If you output the contents of the sql variable to the immediate window with ? sql does it look as expected?

Hi FormR, the dtpFrom and dtpTo is where the date is inputted to filter between dates in DATE BILLED field in the database
And I'm using mysql database

 

[FormR]

Yes, but what is the actual content of those text boxes when the code errors?

I suspect that you need to bookend the dates in the where clause between single quotes - for example:

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 "

But you might also need to change their format.

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

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

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. Thank you for the help. enter image description

stackoverflow.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[geloader0]

Yes, but what is the actual content of those text boxes when the code errors?

I suspect that you need to bookend the dates in the where clause between single quotes - for example:

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 "

But you might also need to change their format.

Yes, I tried to put a single quotes on it and No error comes up but it displays no data with the date range of dtpFrom and dtpTo. Anyway, this is the data of DATE BILLED look likes.

 

[FormR]

what is the actual content of those text boxes when the code errors?

I think it would help if you could answer this?

 

[geloader0]

I think it would help if you could answer this?

Oh sorry, I didn't noticed that. What do you mean the actual content?

 

[FormR]

Yes, the actual content that is in the text box - what is it exactly?

 

[geloader0]

Yes, the actual content that is in the text box - what is it exactly?

This is the in the form looks like with textboxes