Error[ReSolved]

[Paradox]

I am trying to query a records from a SQL Server Dbase and i get a syntax error. Where am i goin wrong
Thanks

Private Sub Command1_Click()
    rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy") & "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy") & "'AND '" & (Combo1.Text) & "')", Cn, adOpenStatic, adLockReadOnly

[Mike Hildner]

That's pretty tough to look at. Why don't you turn on the profiler and check out the sql that's getting sent. Profiler is a great debugging tool.

[brucevde]

The last criteria needs to be part of an equation. You currently have

& "'AND '" & (Combo1.Text) & "')", cn

Add the database field you are checking against the combobox.

Also, insert a space between the single quote and the last And

[Paradox]

I'm Kinda newbie to SQL ope u don mind writin da code.

The err msg still remains.

Incorrect syntax near ')'

Thanks

[Mike Hildner]

brucevde did a lot more than me, but still I suggest you check out the sql that is actually getting sent to the DB. Best way is to use the profiler, if you know how. If not, well, you'll want to learn how to do it (it'll save you tons of time), but you could also set a string to the sql you're going to send, then display the string that will be sent.

[Paradox]

Thanks for your help. let me try using the profiler and see how it goes

[brucevde]

Does Combo1.Text contain a single quote?

Post your modified query.

[Paradox]

Yeah it has a single quote

Private Sub Command1_Click()
    rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy") & "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy") & "'AND ' " & (Combo1.Text) & " ' )", Cn, adOpenStatic, adLockReadOnly

[brucevde]

You need to replace the single quote in Combo1.Text with two single quotes. I prefer to use a variable to hold the sql statement but thats up to you

Dim strSQL as String
 
strSQL = "SELECT * FROM Requests WHERE " _ 
& "((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy")  _ 
& "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy")  _ 
& "' AND SomeDatabaseField = '" & Replace(Combo1.Text,"'","''") & "')", 
 
rsrequests.Open strSQL, Cn, adOpenStatic, adLockReadOnly

The statement sent to SQL Server should look something like

SELECT * FROM Requests WHERE ((DateReq) Between '11 Nov 2004' AND '11 Nov 2004' AND SomeDatabaseField = 'O''Brien')

[Paradox]

brucevde and Mike Hildner Thanks alot.
Dont know how much you guys helped me out. You are appreceiated