ODBC Call failed

[Guest]

Hi,

I have linked tables within Access to two external databases - and have two
problems.

1. When I run a query without any criteria from one table I can get records
returned.

When I try to run a query with the following criteria in the date field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in the query I
don't get any results (they are linked by a common field with a unique ID per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Jeff Boyce]

Could you provide the SQL statement(s)?

 

[Guest]

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID, PSI_PRO_SoldServices1.Order_Type,
PSI_PRO_SoldServices1.Duration__Months_, PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And [End
Date]));

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and have two
problems.

1. When I run a query without any criteria from one table I can get records
returned.

When I try to run a query with the following criteria in the date field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in the query I
don't get any results (they are linked by a common field with a unique ID per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Jeff Boyce]

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start Date] & # And # & [End
Date] & #));

What do you get if you build the query in the query design window, then look
at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID, PSI_PRO_SoldServices1.Order_Type,

PSI_PRO_SoldServices1.Duration__Months_,

PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And [End
Date]));

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and

have
two

problems.

1. When I run a query without any criteria from one table I can get records
returned.

When I try to run a query with the following criteria in the date field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in the

query
I

don't get any results (they are linked by a common field with a unique

ID
per

record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Guest]

Hi,

Thanks for the suggestion - unfortunately it didn't work - I now have a
message which says syntax error in date. The query was built through the
query design window.

A suggestion has been made that the originating database stores dates/times
in seconds from 1970 (but displays as dd/mm/yyyy:hh:mm:ss) - the linked table
field has been changed to read short date format, as has the query.

Would this have something to do with the problem?

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start Date] & # And # & [End
Date] & #));

What do you get if you build the query in the query design window, then look
at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID, PSI_PRO_SoldServices1.Order_Type,

PSI_PRO_SoldServices1.Duration__Months_,

PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And [End
Date]));

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and have
two
problems.

1. When I run a query without any criteria from one table I can get
records
returned.

When I try to run a query with the following criteria in the date field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in the query
I
don't get any results (they are linked by a common field with a unique ID
per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Jeff Boyce]

?Originating database? Do you mean you imported data into Access from some
other system?

Jeff

Hi,

Thanks for the suggestion - unfortunately it didn't work - I now have a
message which says syntax error in date. The query was built through the
query design window.

A suggestion has been made that the originating database stores dates/times
in seconds from 1970 (but displays as dd/mm/yyyy:hh:mm:ss) - the linked table
field has been changed to read short date format, as has the query.

Would this have something to do with the problem?

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start Date] &

#
And # & [End

Date] & #));

What do you get if you build the query in the query design window, then look
at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID, PSI_PRO_SoldServices1.Order_Type,

PSI_PRO_SoldServices1.Duration__Months_,

PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And [End
Date]));


:

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and have
two
problems.

1. When I run a query without any criteria from one table I can get
records
returned.

When I try to run a query with the following criteria in the date field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in

the
query

I
don't get any results (they are linked by a common field with a

unique
ID

per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Guest]

Yes

?Originating database? Do you mean you imported data into Access from some
other system?

Jeff

Hi,

Thanks for the suggestion - unfortunately it didn't work - I now have a
message which says syntax error in date. The query was built through the
query design window.

A suggestion has been made that the originating database stores dates/times
in seconds from 1970 (but displays as dd/mm/yyyy:hh:mm:ss) - the linked table
field has been changed to read short date format, as has the query.

Would this have something to do with the problem?

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start Date] & #
And # & [End
Date] & #));

What do you get if you build the query in the query design window, then look
at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID,
PSI_PRO_SoldServices1.Order_Type,
PSI_PRO_SoldServices1.Duration__Months_,
PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And [End
Date]));


:

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and
have
two
problems.

1. When I run a query without any criteria from one table I can get
records
returned.

When I try to run a query with the following criteria in the date
field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in the
query
I
don't get any results (they are linked by a common field with a unique
ID
per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Jeff Boyce]

After importing the date info, what datatype did Access assign to that
field?

Jeff Boyce
<Access MVP>

Yes

?Originating database? Do you mean you imported data into Access from some
other system?

Jeff

Hi,

Thanks for the suggestion - unfortunately it didn't work - I now have a
message which says syntax error in date. The query was built through the
query design window.

A suggestion has been made that the originating database stores dates/times
in seconds from 1970 (but displays as dd/mm/yyyy:hh:mm:ss) - the

linked
table

field has been changed to read short date format, as has the query.

Would this have something to do with the problem?

:

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start

Date] &
#

And # & [End
Date] & #));

What do you get if you build the query in the query design window,

then
look

at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name, PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID,
PSI_PRO_SoldServices1.Order_Type,
PSI_PRO_SoldServices1.Duration__Months_,
PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date]

And
[End

Date]));


:

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and
have
two
problems.

1. When I run a query without any criteria from one table I

can
get

records
returned.

When I try to run a query with the following criteria in the date
field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB)

in
the

query
I
don't get any results (they are linked by a common field with

a
unique

ID
per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Guest]

Date/Time

(I've also found out that someone else is having a similar problem with a
Crystal report to the same external db - which is an Oracle system - queries
to the SQL system are no problem.)

After importing the date info, what datatype did Access assign to that
field?

Jeff Boyce
<Access MVP>

Yes

?Originating database? Do you mean you imported data into Access from some
other system?

Jeff

Hi,

Thanks for the suggestion - unfortunately it didn't work - I now have a
message which says syntax error in date. The query was built through the
query design window.

A suggestion has been made that the originating database stores
dates/times
in seconds from 1970 (but displays as dd/mm/yyyy:hh:mm:ss) - the linked
table
field has been changed to read short date format, as has the query.

Would this have something to do with the problem?

:

I suspect you need to do something like ...

WHERE (((PSI_PRO_SoldServices1.Create_Date) Between # & [Start Date] &
#
And # & [End
Date] & #));

What do you get if you build the query in the query design window, then
look
at the SQL statement via the SQL design window?

--
Good luck

Jeff Boyce
<Access MVP>

Hi, here it is..

SELECT PSI_PRO_SoldServices1.Create_Date,
PSI_PRO_SoldServices1.Account_Name,
PSI_PRO_SoldServices1.Sold_Service_ID,
PSI_PRO_SoldServices1.Old_Sold_Service_ID,
PSI_PRO_SoldServices1.Order_Type,
PSI_PRO_SoldServices1.Duration__Months_,
PSI_PRO_SoldServices1.Product_Name,
PSI_PRO_SoldServices1.Unit_By_Total
FROM PSI_PRO_SoldServices1
WHERE (((PSI_PRO_SoldServices1.Create_Date) Between [Start Date] And
[End
Date]));


:

Could you provide the SQL statement(s)?

--
More info, please ...

Jeff Boyce
<Access MVP>

Hi,

I have linked tables within Access to two external databases - and
have
two
problems.

1. When I run a query without any criteria from one table I can
get
records
returned.

When I try to run a query with the following criteria in the date
field I
get ODBC Call failed error message:

Between [Start Date] and [End Date]

2. If I take data from two tables (from the same original DB) in
the
query
I
don't get any results (they are linked by a common field with a
unique
ID
per
record in that field).

Any suggestions as to how to get round 1 or 2 please?

Many thanks

 

[Van T. Dinh]

I think the O.P. actually meant linked Table, not imported Table since if it
were imported, the O.P. shouldn't have had the ODBC Call fail error as he
would query the local (imported Table).

 

[Van T. Dinh]

1. Firstly, check and make sure this is a normal (Access/JET) Select Query
with ODBC-linked Table as the data source and not a Pass-Through Query.

2. My guess is that Access thinks that the ODBC understands the Between ...
And ... construct and Access passes this to the Oracle database engine for
processing.

(I don't know about Oracle) but it is possible that Oracle has Between ...
And ... but the 2 arguments need to be in a suitable format for Oracle, e.g.
number of secs from 1970. If this is the case, try using the 2 dates in the
Between ... And ... construct in a format suitable to Oracle.

 

[Jeff Boyce]

Nice catch, Van!

Jeff

I think the O.P. actually meant linked Table, not imported Table since if it
were imported, the O.P. shouldn't have had the ODBC Call fail error as he
would query the local (imported Table).

 

[Guest]

How was this fixed.....I am having a similar problem....I am getting and ODBC
Failed. If I enter in just one date I get data, if I enter in no date I get
data, but if I enter in a range using the between it does not work.

Help??

Thanks,
Brian