Search between two dates mysql

Hi,

I want to search the data between two dates.

[code] Dim sql As String

if CustomerSearchFor.text<>"" then
sql = “SELECT * FROM “+ComboBox1.Text+”_report”+" WHERE date LIKE ‘%" + CustomerSearchFor.Text + "%’"
else
sql=“SELECT * FROM “+ComboBox1.Text+” WHERE Date BETWEEN’” + CustomerSearchFor.text + “’ AND '” + CustomerSearchFor1.text + “’”
end if[/code]

I have found no error, but the result only shows from this code

 sql = "SELECT * FROM "+ComboBox1.Text+"_report"+" WHERE date LIKE '%"  + CustomerSearchFor.Text + "%'"

seems that this code is not executed,

    sql="SELECT * FROM "+ComboBox1.Text+" WHERE Date BETWEEN'" + CustomerSearchFor.text + "' AND '" + CustomerSearchFor1.text + "'"

what I am doing wrong here ?

thanks
Regards
Arief

Seems that _report is missing from the last statement.

What database do you use?

Don’t use “like” with dates. What is the date format of CustomerSearchFor? You need to put the value into a date property and then use the SQLDate value. See for instance https://www.w3schools.com/Sql/sql_dates.asp for date searching.

Here’s a snippet I use with MySQL. I found specifying the time necessary.

// t. created_on is a MySQL datetime field. StartDate and EndDate are Xojo date objects // sql = sql + " AND t.created_on BETWEEN '" + StartDate.SQLDate + " 00:00:00' AND '" + EndDate.SQLDate + " 23:59:59.997'"

[quote=389548:@Beatrix Willius]What database do you use?

Don’t use “like” with dates. What is the date format of CustomerSearchFor? You need to put the value into a date property and then use the SQLDate value. See for instance https://www.w3schools.com/Sql/sql_dates.asp for date searching.[/quote]

Customersearchfor is a textfield, I want to populate it by Today’s date in SQLdate Format.

Thanks
Arief

[quote=389556:@Tanner Lee]Here’s a snippet I use with MySQL. I found specifying the time necessary.

// t. created_on is a MySQL datetime field. StartDate and EndDate are Xojo date objects // sql = sql + " AND t.created_on BETWEEN '" + StartDate.SQLDate + " 00:00:00' AND '" + EndDate.SQLDate + " 23:59:59.997'"[/quote]

Its Nice…!
But I put the time in a different column for some reason.

Thanks
Arief