Invalid SQL statement on some machines only

  • Thread starter Thread starter Nikos Yannacopoulos
  • Start date Start date
N

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,

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
 
Nikos Yannacopoulos said:
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
Click to expand...

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") & "'"
 
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
 
Nikos Yannacopoulos said:
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
Click to expand...

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 said:
It is indeed DATETIME.
Click to expand...

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!
 
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
 
Nikos Yannacopoulos said:
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
Click to expand...

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.
 
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
 
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
 
Back
Top