Need help with SQL Query.

[yobensh]

Hi Experts,

I'm trying to filter some data using SQL query on Access 2000 db.
Thing is that I'm getting run-time error '3001' - "Arguments are of the wrong type, are out of acceptable range, or in conflict with one another"
here is what I have so far:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSql As String
Dim day1 As Variant, day2 As Variant

cn.Open "DSN=Process"
rs.Open "Data ORDER BY [Line] ASC", cn, adOpenKeyset, adLockOptimistic

If IsNull(frmData.dtpFrom.Value) = True Then
    day1 = frmData.dtpFrom.MinDate
Else
    day1 = frmData.dtpFrom.Value
End If

day2 = frmData.dtpTo.Value
'On Error GoTo err

day1 = "#" & day1 & " 00:00:01#"
day2 = "#" & day2 & " 23:59:59#"

sSql = "Line like 'LINE-2%' AND (Side='P.S.' OR Side='C.S.') AND Date BETWEEN (" & day1 & " AND " & day2 & ")"
rs.Filter = sSql

Can anyone tell me what I'm doing wrong here?
Thanks,
Yoav

 

[johnwm]

Do a 'Debug.Print sSQL' just before your rs.Filter. Usually the problem is obvious then - if not, post the result of the Debug statement here

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Andrzejek]

Dates in Access should have # around them, don't they? Also, do you turn the Filter off (if you have it somewhere else)?

--- Andy

 

[yobensh]

Hi John,

Unfortunately, I still can't figure out what's wrong..
Here is the result from the debug.print:

Line like 'LINE-2%' AND (Side='P.S.' OR Side='C.S.') AND Date BETWEEN (#23/07/2006 00:00:01# AND #26/07/2006 23:59:59#)

I suspect it's coming from the clause (Side='P.S.' OR Side='C.S.')
Any idea?

 

[johnwm]

Looks like non-US date format to me!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[yobensh]

Right....I'm in Israel

 

[Golom]

Possible Line is a reserved word in Access SQL.

sSql = "[Line] like 'LINE-2%' AND Side IN ('P.S.','C.S.') AND Date BETWEEN (#" & day1 & "# AND #" & day2 & "#)"

 

[Andrzejek]

Why don't you take what's in your Filter and pass it to your original SQL in Access and see if it will work and what will happen:

Select * from SomeTable Where[b]
Line like 'LINE-2%' AND (Side='P.S.' OR Side='C.S.') AND Date BETWEEN (#23/07/2006 00:00:01# AND #26/07/2006 23:59:59#)
[/b]

HTH

---- Andy

 

[johnwm]

What I meant was that you should change you query date order to something that JETSql can understand. Use either #07/23/2006# or #2006-07-23#

If you construct your Access query in Design view using UK (or your own local) date format, and then view the query in SQL view, you will find it's translated into US format

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[yobensh]

If you will look at my code that I posted first, you will see that "day1" and "day2" alreay have the "#" before and after those variables, therefore no need to add it again in the SQL..
I'll try the "IN" thing and will keep you updated with the results.
Thanks again to you all!
Yoav.

 

[johnwm]

It's not about the #'s - it's about dd-mm-yyyy. Please re-read my last post.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[JoeAtWork]

I believe that ADO uses a different SQL syntax than Access. Try putting your data parameters in a string (I realize that in Access you would surround them with #'s). Something like 'July 26, 2006'