Queries for MSSQL Server

[Kukurydz]

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records
from MSSQL table with date and time values from given range. The difficulty
is mainly caused by the fact, that Date and Time is stored in separate rows
as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

[Trevor Best]

Kukurydz wrote:

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records
from MSSQL table with date and time values from given range. The difficulty
is mainly caused by the fact, that Date and Time is stored in separate rows
as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

Get the DBA fired, get a real DBA who can make a proper database.

This abomination will cause everyone who comes into contact with it
problems until the end of time or until you follow the advice above,
whichever comes first.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Marvin Henry]

I would think that you need to try and turn the strings into dates before
you try to filter for the ones you want...especially if you are wanting a
range(from date x to date y)

I don't know why the cdate() didn't work on the big table but I am guessing
that is has something to do with the size of the table and the fact that it
is coming in via ODBC.

Is there any way to query the data first in MSSQL before bringing it into
access?
"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records from MSSQL table with date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

[Marvin Henry]

"Trevor Best" <nospam@localhost> wrote in message
news:41***********************@auth.uk.news.easyne t.net...

Kukurydz wrote:

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records from MSSQL table with date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked well only during tests on small table (about 3000 records). When I linked the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

Get the DBA fired, get a real DBA who can make a proper database.

This abomination will cause everyone who comes into contact with it
problems until the end of time or until you follow the advice above,
whichever comes first.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

tell me about it. I'm not a dba but I know enough to know not to go puttin
dates and times in as text. (I'm available by the way if you want to hire
me.)

[Sherwood Wang]

"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records from MSSQL table with date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

is very bad man who is made your mssql table now is time for GET EVEN!

msql will help get even time ok? goto bad table to open design view. you
change text data type to datetime please.

i make easy example now--

MyDateString = varchar (50)

please now you change--

MyDateString = datetime (8)

please now save. mssql server say in english--

Warning: Data may be lost converting column 'MyDateString' from
'varchar(50)'

is ok for you say ok. goto access please refresh table link in linking
manager. open access table view now you see is REAL dates! is better say
nothing to dba ok? you get even BIG TIME yes?

*Sherwood Wang MVP*

[Larry Linson]

"Sherwood Wang" <sh****@waynes.net> wrote

is very bad man who. . .
you get even BIG TIME yes?

Just for the information of anyone who might be misled, "Sherwood Wang" is
not a Microsoft Access MVP. The list of Microsoft MVPs can be viewed at
http://mvp.support.microsoft.com/def...le=toc#faq1152.

There is a Microsoft Access MVP named Yu Hong Wang, but the referenced post
is not by him; it is from just another sockpuppet of the resident troll and
disruptive poster. MVP Yu Hong Wang would never advise an Access user to
"get even" with a DBA by altering a business database, no matter how poorly
the database seemed to be designed.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:vdTWc.2385$2F.2082@trnddc05...

"Sherwood Wang" <sh****@waynes.net> wrote

> is very bad man who. . .
> you get even BIG TIME yes?
Just for the information of anyone who might be misled, "Sherwood Wang" is
not a Microsoft Access MVP. The list of Microsoft MVPs can be viewed at

http://mvp.support.microsoft.com/def...le=toc#faq1152.
There is a Microsoft Access MVP named Yu Hong Wang, but the referenced post is not by him; it is from just another sockpuppet of the resident troll and disruptive poster. MVP Yu Hong Wang would never advise an Access user to
"get even" with a DBA by altering a business database, no matter how poorly the database seemed to be designed.

Larry Linson
Microsoft Access MVP

I don't see any real difference between Wangs. Why is one Wang any better
than another?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[kukurydz]

"Sherwood Wang" <sh****@waynes.net> wrote in message news:<eW*******************@hydra.nntpserver.com>. ..

"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select

records

from MSSQL table with date and time values from given range. The

difficulty

is mainly caused by the fact, that Date and Time is stored in separate

rows

as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

is very bad man who is made your mssql table now is time for GET EVEN!

msql will help get even time ok? goto bad table to open design view. you
change text data type to datetime please.

i make easy example now--

MyDateString = varchar (50)

please now you change--

MyDateString = datetime (8)

please now save. mssql server say in english--

Warning: Data may be lost converting column 'MyDateString' from
'varchar(50)'

is ok for you say ok. goto access please refresh table link in linking
manager. open access table view now you see is REAL dates! is better say
nothing to dba ok? you get even BIG TIME yes?

*Sherwood Wang MVP*

Life is not so easy...
I can't simply change the format of Date and Time rows, because a new
record is inserted every minute. Data is inserted by a SCADA system
(Wonderware InTouch). There is no MSSQL's datetime compatible variable
in InTouch. Perhaps someone could explain me how to create it.

[David W. Fenton]

The post with MessageID <vm*******************@hydra.nntpserver.com>
is a forgery.

I did not write it.

It shouldn't be very hard for anyone to look at my posts and see
that the headers of the forged post are completely inconsistent with
any of my posts for the last two years or so.

To Don Mellon: Grow up. Get a life.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Sherwood Wang]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:vm*******************@hydra.nntpserver.com...

"Larry Linson" <bo*****@localhost.not> wrote in message
news:vdTWc.2385$2F.2082@trnddc05...

"Sherwood Wang" <sh****@waynes.net> wrote

> is very bad man who. . .
> you get even BIG TIME yes?

Just for the information of anyone who might be misled, "Sherwood Wang" is not a Microsoft Access MVP. The list of Microsoft MVPs can be viewed at

http://mvp.support.microsoft.com/def...le=toc#faq1152.

There is a Microsoft Access MVP named Yu Hong Wang, but the referenced

post

is not by him; it is from just another sockpuppet of the resident troll

and

disruptive poster. MVP Yu Hong Wang would never advise an Access user to
"get even" with a DBA by altering a business database, no matter how

poorly

the database seemed to be designed.

Larry Linson
Microsoft Access MVP

I don't see any real difference between Wangs. Why is one Wang any better
than another?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

is because 2 wangs dont make a wong!

is joke yes?

*Sherwood Wang MVP*

[Sherwood Wang]

"kukurydz" <tu************@gazeta.pl> wrote in message
news:63**************************@posting.google.c om...

"Sherwood Wang" <sh****@waynes.net> wrote in message

news:<eW*******************@hydra.nntpserver.com>. ..

"Kukurydz" <nospam.tu************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in MSAccess. I've got a problem with creating a query which will select

records

from MSSQL table with date and time values from given range. The

difficulty

is mainly caused by the fact, that Date and Time is stored in separate

rows

as String . I've tried to use CDate() function in my query, but it worked well only during tests on small table (about 3000 records). When I linked the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings (.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

is very bad man who is made your mssql table now is time for GET EVEN!

msql will help get even time ok? goto bad table to open design view. you change text data type to datetime please.

i make easy example now--

MyDateString = varchar (50)

please now you change--

MyDateString = datetime (8)

please now save. mssql server say in english--

Warning: Data may be lost converting column 'MyDateString' from
'varchar(50)'

is ok for you say ok. goto access please refresh table link in linking
manager. open access table view now you see is REAL dates! is better say nothing to dba ok? you get even BIG TIME yes?

*Sherwood Wang MVP*

Life is not so easy...
I can't simply change the format of Date and Time rows, because a new
record is inserted every minute. Data is inserted by a SCADA system
(Wonderware InTouch). There is no MSSQL's datetime compatible variable
in InTouch. Perhaps someone could explain me how to create it.

sorry to hear wonderware intouch! is ok idea make tmp tbl in mssql or
access? if is true make tmp tbl hold real date time & other data you need 4
rpt. access coerce bad date time to good date time or use cdate() to make
good date time when write out to tmp tbl. you run rpt qry on good data in
tmp tbl ok?

[Sherwood Wang]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

The post with MessageID <vm*******************@hydra.nntpserver.com>
is a forgery.

I did not write it.

It shouldn't be very hard for anyone to look at my posts and see
that the headers of the forged post are completely inconsistent with
any of my posts for the last two years or so.

To Don Mellon: Grow up. Get a life.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

so sorry mr david f fenton i make joke on forgery. i look 2 late & see you
post! mr don mellon is very bad children.

*Sherwood Wang MVP*

[Scott Berry]

CDate() will fail if there are any NULL values in the date fields on the
Server. Try CVDate() instead.

SB
"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select
records
from MSSQL table with date and time values from given range. The
difficulty
is mainly caused by the fact, that Date and Time is stored in separate
rows
as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

[david epsom dot com dot au]

if you need to convert the data before applying the criteria,
you will have to convert ALL of the data EVERYTIME you apply
the criteria. And you won't be able to take advantage of
indexes when applying the criteria.

If you can't do it any other way, you should use additional
criteria on the text fields: for example: sDate > "12"
Criteria written like this will be applied by the server,
before your date conversion is required.

This can limit the number of records that where you need to
convert to dates.

(david)

"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records from MSSQL table with date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).

[Terry Kreft]

Look at using a PassThrough query instead. The work of filtering the data is
then carried out on the server by SQL Server and only yhe filtered set is
returned to Access.

--
Terry Kreft
MVP Microsoft Access
"Kukurydz" <no*******************@gazeta.pl> wrote in message
news:cg**********@inews.gazeta.pl...

I've got such problem:
My database is stored on MSSQL Server. I have to write reports for it in
MSAccess. I've got a problem with creating a query which will select records from MSSQL table with date and time values from given range. The difficulty is mainly caused by the fact, that Date and Time is stored in separate rows as String . I've tried to use CDate() function in my query, but it worked
well only during tests on small table (about 3000 records). When I linked
the MSSQL Server's table through ODBC it did not work. It did not work
either with imported table (it took some time to import it, as it contains
over 200000 records). My test table (the small one) was created by a
table-making query from the big one. I just selected 3 dates as strings
(.... WHERE ((tbName.Date = "12/07/2004") OR () OR () ...).