473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Queries for MSSQL Server

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 () ...).
Nov 13 '05 #1
14 2430
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.
\__\

Nov 13 '05 #2
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 () ...).

Nov 13 '05 #3

"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.)
Nov 13 '05 #4

"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*



Nov 13 '05 #5
"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


Nov 13 '05 #6

"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

Nov 13 '05 #7
"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.
Nov 13 '05 #8
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
Nov 13 '05 #9

"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*


Nov 13 '05 #10

"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?

Nov 13 '05 #11

"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*

Nov 13 '05 #12
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 () ...).

Nov 13 '05 #13
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 () ...).

Nov 13 '05 #14
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 () ...).

Nov 13 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Lars Nielsen | last post by:
I'm trying to execute a long query with mssql_query(), but it fails after 30 seconds. I've tried to use ini_set in my script : ini_set("mssql.timeout", "120"); Both just before the query and...
7
by: mj | last post by:
Hello, thanks for the help. I am running a WinXP Pro w/ SP2 (my home computer, with ZoneAlarm firewall) Apache 2.0.52 MySQL 4.1.7 PHP 5.1.0-dev I have developed a PHP/MySQL web app that...
3
by: gharmel | last post by:
I'm trying to get some clues on why I get (much) slower responses from my PHP applications when dealing with a remote sql server as opposed to a local sql server. Here's my situation: Server...
0
by: intergroove | last post by:
Help. This has been bugging me for a couple of days now: I am writing a script to regularly transfer data from a MYSQL db to a MSSQL. Being new to MSSQL I'm a bit freaked out about the...
6
by: Peter Plate | last post by:
Hi all. I have a system which operates on Windows MSSQL. It is used for registering Suppotr requests. The system works with different Tables inside a Database. One of the tables is for new...
7
by: Olegus | last post by:
Hello, in order to perform backup/restore MSSQL database using SMO, one needs to reference several namespaces in a backup class : using Microsoft.SqlServer.Management.Common; using...
2
by: gnomee2 | last post by:
Hello Everyone, I have a strange problem that I cannot seem to solve. I have two server running Windows 2003 MSSQL on one IIS on the other. Out of the blue I have slow queries that cause asp...
1
by: tgphelps | last post by:
I've got this 20,000 row table. It had never given anyone trouble. I'm not a particularly good SQL Server admin, but I'm all we have. I was certified on MSSQL 7, some years ago, but it's just a...
3
by: buntyindia | last post by:
Hi DB2 Gurus, I have a application that using MSSQL database . Now we have to migrate to DB2. I have all the queries. Guide me how to make the MSSQL queries compatible to DB2 is there any...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.