Sql Between ??

[hrast]

Hello !

I have 2 fields:

Date1 = txtDateFrom.Text
Date2 = txtDateTo.Text

I would like to use this SQL query:

"SELECT * FROM Base WHERE Date BETWEEN &Date1 AND &Date2"

It doesn't work! Why?

[Twodogs]

Try
[code]
dim strSql as string
dim Date1 as string
dim Date2 as string

strSql = "SELECT * FROM Base WHERE Date BETWEEN '" & Date1 & "' AND '" & Date2 & "'"

[hrast]

It says:

Data type mismatch in criteria expression!

This is my code:

Private Sub cmdQuery_Click()

If (IsDate(txtDateFrom.Text)) And (IsDate(txtDateTo.Text)) Then


Ado.ConnectionString = Baza
Ado.CursorLocation = adUseClient
Ado.CommandType = adCmdText
Ado.RecordSource = "Table1"
Ado.CursorType = adOpenStatic
Ado.LockType = adLockOptimistic

Dim Date1 As Date
Date1 = txtDateFrom.Text

Dim Date2 As String
Date2 = "#" & Month(Date1) & "/" & Day(Date1) & "/" & Year(Date1) & "#"

Dim Date3 As Date
Date3 = txtDateTo.Text

Dim Date4 As String
Date4 = "#" & Month(Date3) & "/" & Day(Date3) & "/" & Year(Date3) & "#"


AdoZaObdobje.RecordSource = "SELECT * FROM Table1 WHERE Date BETWEEN '" & Date2 & "' AND '" & Date4 & "'"

Ado.Refresh

End Sub

[doofusboy]

When "it" says 'Data type mismatch', "it" is absolutely correct.

The reason is you declare Date1 and Date3 as Date data types and you declare Date2 and Date4 as String data types. Then, in your SQL statement, you try to use the 2 strings as Dates. Just because you format the strings to look like dates to human eyes, doesn't mean "it" sees them as dates.

Try using Date1 and Date3 in your SQL query. Also, I don't think dates need the single quote marks around them like strings do.

So you would need something like:
"SELECT * FROM Table1 WHERE Date BETWEEN " & Date2 & " AND " & Date4 & ";"

Depending on the database you are using you may need to use something like this:
"SELECT * FROM Table1 WHERE Date BETWEEN " & #Date2# & " AND " & #Date4# & ";"

[Twodogs]

Doofusboy is quite correct, however I have found that I always have trouble in SQL Server if I attempt to send a Date data type through. I have found that sending it through as a string, in the format 'dd mmm yyyy hh:nn:ss AM/PM' works because it (SQL Server) can carry out an implicit conversion on those dates.

I think the problem arises (for me) because I have my local machine set to australian format dates, whereas the Date data type holds the date as mm/dd/yyyy hh:nn:ss (or something similar).

Funnily enough though, sending the dates enclosed by #dd/mmm/yyyy# seems to work for me in MS Access - probably because Access is running on a local machine and picks up its date format from the local machine, whereas SQL Server stores its dates in the US format. Weird....

[hrast]

Thanks, it works !

[hrast]

I would like to try another condition like this:

"SELECT * FROM Table1 WHERE Date BETWEEN " & Date2 & " AND " & Date4 & AND Name = & txtName.text & "

It doesn't work ...any idea ?

[lsmeteor]

Try this:

You need to add single quotes around the Name field because it is straight text.

"SELECT * FROM Table1 WHERE Date BETWEEN " & Date2 & " AND " & Date4 & AND Name = '" & txtName.text & "'"

[hrast]

It says syntax error !
I don't know why...

Is it right to write BETWEEN and then another condition like txtSomething.Text??

[lsmeteor]

The SQL statement looks fine to me, but you have to be careful in the choice of column names. 'Date' might be a reserved word, same for 'Name'

You might want to try:
"SELECT * FROM Table1 WHERE [Date] BETWEEN " & Date2 & " AND " & Date4 & AND [Name] = '" & txtName.text & "'"

I can't remember for sure what the suntax to force a column name is.
hth

[Twodogs]

Change your condition around, so you have the explicit condition first, then the between conditions. You may also need to put parenthesis around the conditions too.

"SELECT * FROM Table1 WHERE Name = '" & txtName.text & "' AND (Date BETWEEN " & Date2 & " AND " & Date4 & ")"

I'd also strongly suggest that you change the fieldnames "Name" and "Date" to something else - they are both reserved words in SQL - what you *should* be doing is

"SELECT * FROM Table1 WHERE [Name] = '" & txtName.text & "' AND ([Date] BETWEEN " & Date2 & " AND " & Date4 & ")"

which tells sql server/access/whatever that they are column names and not calls to methods.

[hrast]

Thank you doofusboy, Ismeteor and Twodogs.

It's working now, with your help...

Thank you..