Major report problem

[Guest]

Hi,

I'm develpoing an application using Access XP. The database is sql server
2000 and I'm connecting the tables using ODBC.

I have a few reports that i filter using a simple criteria in VB using the
"where" clause in docmd.openreport and it usually works OK.
As of yesterday, the BIG problem is this: when i filter the report on an int
column: (col between 2 and 9) it works great.
but when i filter the report using a date column: (dateCol between
#02/02/2007# And #09/09/2007#) it works on my access and crashes the runtime
on other computers.
The reports contain code for a msgbox when the NoData event occurs - nothing
other.

I've installed XP runtime to all clients including all the latest updates &
sp available.

ideas anyone ?

 

[Jeff Boyce]

Daniel

What is the datatype of the date column in SQL-Server?

Is this a new problem (it used to work, and suddenly doesn't) or has it
always not worked?

Have you tried creating a "view" on the SQL-Server side ... does that work?


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi,

1. The data type is SmallDateTime
2. Yes, it worked before
3. I tried that now - didn't help... :-(

any other ideas ?

 

[Guest]

Daniel,

You wrote that it "used to work." Is that also true for passed Date values
or only Int values? The reason I ask is that SQL Server uses " ' " for
delimiting Dates, not " # " as does Access. IOW, to use your example:

(dateCol between '02/02/2007' And '09/09/2007')

This may not fix your problem, but at least it's worth a try.

Good luck,

Ed

 

[Guest]

Hi Ed,

It used to work with int values and with date values. currently - int works
OK, Date crashes the application.
regarding the " ' " vs "#" youre right when working on ADP file or using ADO
connections. on MDB file (or DAO) youre working with JET standards.
either way - This way works with other reports in the application

another option maybe ?

 

[Jeff Boyce]

Daniel

What does #3 mean? ("didn't help")

Does that mean you are not able to create a SQL-Server view that correctly
returns rows?

What is the SQL statement you are using, either for the view in SQL-Server
or for your query in Access?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

#3 - means I tried to create a view and base the report on it - but it did
the same.
and basically - i'm using access querys that retrieve data from sql server
tables

But - here's something tried recently - I filtered the date column using
float numbers: "(date_col between " & cdbl(FromDate) & " And " & cdbl(ToDate)
&")"
this works partially - it displays the report filtered wrong - but it's not
crashing the runtime.

 

[Jeff Boyce]

Daniel

I'll bow out at this point. I've offered the notions I can come up with.

Hopefully one of the other newsgroup readers has run across this and can
help you out.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/