Reserved Error (-7711); there is no message for this error

[Mark Eden]

I am trying to pick out data from an OBDC Source so i have created a
link table to it. I have created a query to look at the data in this
table. I have chosen all the fields that i require. There is a date
field in there which looks at sets of data for a week. If i go into
the query design and actually type in the criteria of the date in
this format it works year-month-day (2004-02-09).

So i have tried to put a text box in to be able to type the date in
and be able to search for. This doesnt work. This is the SQL i am
using. Its really basic yet cant see the problem!

SELECT CTCH_WK.CELLID, CTCH_WK.WEEKOF, CTCH_WK.BSCID,
CTCH_WK.TOT_NORMATTS, CTCH_WK.TOT_NORMSEIZ, CTCH_WK.TOT_RFLOSSES,
CTCH_WK.TOT_MISCDROP, CTCH_WK.TOT_DROPQDL, CTCH_WK.TOT_DROPQUL,
CTCH_WK.TOT_DROPSSDL, CTCH_WK.TOT_DROPSSUL, CTCH_WK.TOT_DROPTA INTO
CTCH_WK_Q
FROM CTCH_WK
WHERE (((CTCH_WK.WEEKOF)=[Type In The Week Eg 2004-02-09]));

Can anyone see why it doesnt work, but works fine if you put it
directly into the query. Obviously i dont want to do that for every
query i want to run as it would be a pain!

[Mike Storr]

On 19 Feb 2004 07:04:52 -0800, Mark Eden wrote:

I am trying to pick out data from an OBDC Source so i have created a
link table to it. I have created a query to look at the data in this
table. I have chosen all the fields that i require. There is a date
field in there which looks at sets of data for a week. If i go into
the query design and actually type in the criteria of the date in
this format it works year-month-day (2004-02-09).

So i have tried to put a text box in to be able to type the date in
and be able to search for. This doesnt work. This is the SQL i am
using. Its really basic yet cant see the problem!

SELECT CTCH_WK.CELLID, CTCH_WK.WEEKOF, CTCH_WK.BSCID,
CTCH_WK.TOT_NORMATTS, CTCH_WK.TOT_NORMSEIZ, CTCH_WK.TOT_RFLOSSES,
CTCH_WK.TOT_MISCDROP, CTCH_WK.TOT_DROPQDL, CTCH_WK.TOT_DROPQUL,
CTCH_WK.TOT_DROPSSDL, CTCH_WK.TOT_DROPSSUL, CTCH_WK.TOT_DROPTA INTO
CTCH_WK_Q
FROM CTCH_WK
WHERE (((CTCH_WK.WEEKOF)=[Type In The Week Eg 2004-02-09]));

Can anyone see why it doesnt work, but works fine if you put it
directly into the query. Obviously i dont want to do that for every
query i want to run as it would be a pain!

Normally when using dates as criteria, Access prefers (I think requires)
that dates are surrounded with # - eg #02/18/04#. Have yuo tried formatting
the Where statement as

WHERE (((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#));

--
Mike Storr
www.veraccess.com

[PaulT]

You may need to add the '#' operators to both ends of the date for it
to be used.

Also, you can find a list of ODBC errors here:
http://msdn.microsoft.com/archive/de...l/msdn_odk.asp

-Paul

ma*******@t-mobile.co.uk (Mark Eden) wrote in message news:<cd**************************@posting.google. com>...

I am trying to pick out data from an OBDC Source so i have created a
link table to it. I have created a query to look at the data in this
table. I have chosen all the fields that i require. There is a date
field in there which looks at sets of data for a week. If i go into
the query design and actually type in the criteria of the date in
this format it works year-month-day (2004-02-09).

So i have tried to put a text box in to be able to type the date in
and be able to search for. This doesnt work. This is the SQL i am
using. Its really basic yet cant see the problem!

SELECT CTCH_WK.CELLID, CTCH_WK.WEEKOF, CTCH_WK.BSCID,
CTCH_WK.TOT_NORMATTS, CTCH_WK.TOT_NORMSEIZ, CTCH_WK.TOT_RFLOSSES,
CTCH_WK.TOT_MISCDROP, CTCH_WK.TOT_DROPQDL, CTCH_WK.TOT_DROPQUL,
CTCH_WK.TOT_DROPSSDL, CTCH_WK.TOT_DROPSSUL, CTCH_WK.TOT_DROPTA INTO
CTCH_WK_Q
FROM CTCH_WK
WHERE (((CTCH_WK.WEEKOF)=[Type In The Week Eg 2004-02-09]));

Can anyone see why it doesnt work, but works fine if you put it
directly into the query. Obviously i dont want to do that for every
query i want to run as it would be a pain!

[Mark Eden]

Hi i have tried putting the # signs where you have said but
unfortunately it doesnt work. This is what i changed:

SELECT CTCH_WK.CELLID, CTCH_WK.WEEKOF, CTCH_WK.BSCID,
CTCH_WK.TOT_NORMATTS, CTCH_WK.TOT_NORMSEIZ, CTCH_WK.TOT_RFLOSSES,
CTCH_WK.TOT_MISCDROP, CTCH_WK.TOT_DROPQDL, CTCH_WK.TOT_DROPQUL,
CTCH_WK.TOT_DROPSSDL, CTCH_WK.TOT_DROPSSUL, CTCH_WK.TOT_DROPTA INTO
CTCH_WK_Q
FROM CTCH_WK
WHERE (((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#));
The error i get is "Syntax error in date in query expression
'(((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#))'

Any help

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Eden]

Thanks for that, i have tried what you suggested by doing:

WHERE (((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#));

I get the following error:

"Syntax error in date in query expression 'WHERE
(((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#))'

Any other ideas, i ahve tried when i used the orginal code and when i
type the date in putting # before and after and that doesnt work either.

Didnt think it would but its driving me nuts now!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PaulT]

You may have to do it this way (depending on how your quotes are
arranged)...you may have to try slight variations of this example...

'WHERE (((CTCH_WK.WEEKOF)=' & "#" & [Type In The Week Eg 2004-02-09] &
"#))";

-Paul
Mark Eden <ma*******@t-mobile.co.uk> wrote in message news:<40*********************@news.frii.net>...

Thanks for that, i have tried what you suggested by doing:

WHERE (((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#));

I get the following error:

"Syntax error in date in query expression 'WHERE
(((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#))'

Any other ideas, i ahve tried when i used the orginal code and when i
type the date in putting # before and after and that doesnt work either.

Didnt think it would but its driving me nuts now!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pink Panther]

Mark,

1. what odbc driver are you using?
2. does the query work when you use a date in the WHERE clause as
opposed to a parameter?
3. have you declared a parameter eg

PARAMETERS [Enter Date] DateTime;
SELECT etc, etc

4. have you considered using a pass through query built on the fly?

5. Does a passthrough query with an actual date in the query return
the same error?

in my 2 second test query i did not need the # placeholders.
also I could not repro. your problem with SQL Server ODBC drivers
(well I really didn't expect to!).

Peter

Mark Eden <ma*******@t-mobile.co.uk> wrote in message news:<40*********************@news.frii.net>...

Thanks for that, i have tried what you suggested by doing:

WHERE (((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#));

I get the following error:

"Syntax error in date in query expression 'WHERE
(((CTCH_WK.WEEKOF)=#[Type In The Week Eg 2004-02-09]#))'

Any other ideas, i ahve tried when i used the orginal code and when i
type the date in putting # before and after and that doesnt work either.

Didnt think it would but its driving me nuts now!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!