Invalid SQL statement on some machines only

[Nikos Yannacopoulos]

I have a report in an .adp with an SQL server BE; in the Open event, I
construct a filter string (looking up some controls on the main form)
and apply it as a server filter. It works fine on my machine, but not on
others, with (supposedly) the same Win and Office versions (XP Pro, 2003
respectively) and SP levels, the error message being "Invalid SQL
statement. Check the server filter on the form recordsource". Anyone
seen this before?

Thanks on advance,
Nikos

 

[Malcolm Cook]

would you show us the exact vb you're running?

 

[Nikos Yannacopoulos]

Malcolm,

Here goes:

Private Sub Report_Open(Cancel As Integer)
fltr = "(IncDate BETWEEN " & Format(Forms!frmControlCenter!DateFrom,
"0") - 2 _
& " AND " & Format(Forms!frmControlCenter!DateTo, "0") - 1.000001 & ")"
vSite = Forms!frmControlCenter!cboSite
If vSite <> 0 Then fltr = fltr & " AND (SiteID = " & vSite & ")"
vPersonnel = Forms!frmControlCenter!cboPersonnel
If vPersonnel <> 0 Then fltr = fltr & " AND (AssignedTo = " & vPersonnel
& ")"
Me.ServerFilter = fltr
End Sub

It will probably wrap in the NG reader, it's OKm in the report module.
I've found that I have to convert the Office Date/Time data type to
numeric to make it work with the SQL server date field, plus for some
obscure reason, they seem to have 2 day difference. Ma I doing something
wrong here?

Thanks for looking at it.

Nikos

 

[Baz]

Malcolm,

Here goes:

Private Sub Report_Open(Cancel As Integer)
fltr = "(IncDate BETWEEN " & Format(Forms!frmControlCenter!DateFrom,
"0") - 2 _
& " AND " & Format(Forms!frmControlCenter!DateTo, "0") - 1.000001 & ")"
vSite = Forms!frmControlCenter!cboSite
If vSite <> 0 Then fltr = fltr & " AND (SiteID = " & vSite & ")"
vPersonnel = Forms!frmControlCenter!cboPersonnel
If vPersonnel <> 0 Then fltr = fltr & " AND (AssignedTo = " & vPersonnel
& ")"
Me.ServerFilter = fltr
End Sub

It will probably wrap in the NG reader, it's OKm in the report module.
I've found that I have to convert the Office Date/Time data type to
numeric to make it work with the SQL server date field, plus for some
obscure reason, they seem to have 2 day difference. Ma I doing something
wrong here?

Thanks for looking at it.

Nikos

Hi Nikos,

Handling date literals in SQL Server SQL is quite similar to doing it in
Access, EXCEPT:

- You need to delimit them using single quotes, not the # character
- The only region-independent format is yyyymmdd.

So, if you do something like this, hopefully your problems will go away:

IncDate BETWEEN '" & Format(Forms!frmControlCenter!DateFrom, "yyyymmdd") &
"' AND '" & Format(Forms!frmControlCenter!DateTo, "yyyymmdd") & "'"

 

[Nikos Yannacopoulos]

Baz,

Thanks for this, but it didn't work. Your proposed syntax just didn't
return any results, so I started playing around with the format (I have
non-US settings, and I'm aware of the implications from past
experience). When I inserted slashes betweeen the date parts, I got the
following error message:

Syntax error converting the varchar value '2005/10/01' to a column of
data type int.

which makes me think the SQL Server view indeed expects numeric parameters.

Nikos

 

[Baz]

Baz,

Thanks for this, but it didn't work. Your proposed syntax just didn't
return any results, so I started playing around with the format (I have
non-US settings, and I'm aware of the implications from past
experience). When I inserted slashes betweeen the date parts, I got the
following error message:

Syntax error converting the varchar value '2005/10/01' to a column of
data type int.

which makes me think the SQL Server view indeed expects numeric parameters.

Nikos

So what IS the data type of the column "IncDate"? The name suggests that
it's datetime, but if it isn't, then no-one can really help you until you
tell us what it is (and, if it's not datetime, what values does it actually
contain and what do they mean?)

 

[Nikos Yannacopoulos]

It is indeed DATETIME.

 

[Baz]

It is indeed DATETIME.

So, going back to the code I posted earlier, it obviously works in the sense
that it doesn't give any error. As to why it doesn't return any results, I
really couldn't comment without more information. For all I know, it
doesn't return any results because there are no qualifying rows in the
table!

 

[Nikos Yannacopoulos]

Baz,

There are qualifying records in the table, and my original code returns
them on some machines, while it produces an error on others. This is the
problem (as per my original post), not the code itself.

Code not giving error messages is not the same as code working! Your
code doesn't err because it manages to convert "20051110" to an integer,
as required by SQL server... but returns no records because it results
in an outrageously distant future date. The error message it produces
when slashes are entered in the date strings, proves just that.

Thanks for trying though.

Nikos

 

[Baz]

Baz,

There are qualifying records in the table, and my original code returns
them on some machines, while it produces an error on others. This is the
problem (as per my original post), not the code itself.

Code not giving error messages is not the same as code working! Your
code doesn't err because it manages to convert "20051110" to an integer,
as required by SQL server... but returns no records because it results
in an outrageously distant future date. The error message it produces
when slashes are entered in the date strings, proves just that.

Thanks for trying though.

Nikos

SQL Server does NOT require date criteria to be integers. I do this stuff
all the time and I can assure you of that.

In an earlier post, you said that you got the following message:

"Syntax error converting the varchar value '2005/10/01' to a column of data
type int"

Two points about that:

- firstly, '2005/10/01' is a valid date in any region I am aware of,
either 1st October or 10th January
- secondly, if you specify an invalid date criterion for a datetime
column, the error message you get is this:

"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."

Therefore, your column is NOT a datetime data type. You mentioned a view:
maybe it's datetime in the underlying table, but the view is converting it
to integer for some reason. Anyhow, there is definitely something about
this setup that you have overlooked and hence failed to tell us about.

 

[Nikos Yannacopoulos]

Baz,

You were right, I was using your syntax on a calculated field in my view
(CAST(dbo.Incidents.IncDate AS Int)) on which your syntax erred... sorry
for the confusion, the calculated field name was very similar (IncDay)
so I didn't see it. The reason why I tried this is because the original
datetime field in the table records the time part as well, while I neeed
to filter on the date only.
My original code filtered on the datetime field (IncDate) itself, on
which your code also works, but fails to return records on the upper
limit (last date in range) because of the time part.

Like I said I have non-US defaults because I am in a European country
(Greece, as if my surname didn't give me out); that is the case with all
machines in the office (and, to make it worse, with several variations!)
so I have acquired the habit of converting dates to numbers to resolve
this. This is the reason why I prefer to avoid specific date formats.

Again, this was not my problem! My problem was (and is) why it won't
work on some machines.

Thanks again,

Nikos

 

[Nikos Yannacopoulos]

In case anybody's interested, it turned out to be an installation or
patching issue... not sure what. Even uninstalling and reinstalling
Office didn't resolve it, it took a clean installation of WinXP after
formatting the HD.

Hope the next person with this problem is luckier!

Nikos