ODBC Call Failed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
 
Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));
 
There was no data returned. The dbo_pldb.end_date is data type as text. What
else I am missing? Thank you for your help.

John Spencer (MVP) said:
Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));
I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
Click to expand...
Click to expand...
 
well if your date field is a text field then you are going to have problems.
You might try forcing the field to date by using the CDate function or the
DateValue function. I am surprised that you didn't get a criteria mismatch
error if the End_Date field is text.

WHERE DateValue(dbo_pldb.End_Date) Between ....

Also, are you using dates that are in US format (mm/dd/yyyy) or in the format
YYYY/MM/DD? Access insists that date literals be in one of those formats to get
reliable results.


There was no data returned. The dbo_pldb.end_date is data type as text. What
else I am missing? Thank you for your help.

John Spencer (MVP) said:
Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));
I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
Click to expand...
Click to expand...
Click to expand...
 
My external database dbo_pldb has end_date field which consist data as:
20050501 and when I view this table in design mode the data type is text.

Please help.

John Spencer (MVP) said:
well if your date field is a text field then you are going to have problems.
You might try forcing the field to date by using the CDate function or the
DateValue function. I am surprised that you didn't get a criteria mismatch
error if the End_Date field is text.

WHERE DateValue(dbo_pldb.End_Date) Between ....

Also, are you using dates that are in US format (mm/dd/yyyy) or in the format
YYYY/MM/DD? Access insists that date literals be in one of those formats to get
reliable results.


There was no data returned. The dbo_pldb.end_date is data type as text. What
else I am missing? Thank you for your help.

John Spencer (MVP) said:
Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));

SAL wrote:

I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
Click to expand...
Click to expand...
Click to expand...
 
Ok then try the conversion using one of these

DateValue(Format(dbo_pldb.End_Date,"@@@@\/@@\/@@"))

or
DateSerial(Left(dbo_pldb.End_date),4),Mid(dbo_pldb.End_date,5,2), Right(dbo_pldb.End_date,2))

OR Go back to using a text string and enter your dates in the controls Beg_date
and End_Date in the format yyyymmdd without any separators.
My external database dbo_pldb has end_date field which consist data as:
20050501 and when I view this table in design mode the data type is text.

Please help.

John Spencer (MVP) said:
well if your date field is a text field then you are going to have problems.
You might try forcing the field to date by using the CDate function or the
DateValue function. I am surprised that you didn't get a criteria mismatch
error if the End_Date field is text.

WHERE DateValue(dbo_pldb.End_Date) Between ....

Also, are you using dates that are in US format (mm/dd/yyyy) or in the format
YYYY/MM/DD? Access insists that date literals be in one of those formats to get
reliable results.


There was no data returned. The dbo_pldb.end_date is data type as text. What
else I am missing? Thank you for your help.

:

Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));

SAL wrote:

I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
Click to expand...
Click to expand...
Click to expand...
 
John,

Your Datevalue option 1 worked along with changes to various other data type
in the date forms.

Thank you so much.


John Spencer (MVP) said:
Ok then try the conversion using one of these

DateValue(Format(dbo_pldb.End_Date,"@@@@\/@@\/@@"))

or
DateSerial(Left(dbo_pldb.End_date),4),Mid(dbo_pldb.End_date,5,2), Right(dbo_pldb.End_date,2))

OR Go back to using a text string and enter your dates in the controls Beg_date
and End_Date in the format yyyymmdd without any separators.
My external database dbo_pldb has end_date field which consist data as:
20050501 and when I view this table in design mode the data type is text.

Please help.

John Spencer (MVP) said:
well if your date field is a text field then you are going to have problems.
You might try forcing the field to date by using the CDate function or the
DateValue function. I am surprised that you didn't get a criteria mismatch
error if the End_Date field is text.

WHERE DateValue(dbo_pldb.End_Date) Between ....

Also, are you using dates that are in US format (mm/dd/yyyy) or in the format
YYYY/MM/DD? Access insists that date literals be in one of those formats to get
reliable results.



SAL wrote:

There was no data returned. The dbo_pldb.end_date is data type as text. What
else I am missing? Thank you for your help.

:

Try declaring your parameters as DateTime vice Text and using the ! delimiter
versus the . delimiter for the reference to the controls on the form.

PARAMETERS [forms]![Curr_date]![Beg_Date] DateTime,
[forms]![Curr_date]![End_Date] DateTime;
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date]![Beg_Date] And
[forms]![Curr_date]![End_Date]));

SAL wrote:

I have converted my dabatase97 to 2003 and now I am getting ODBC Call Failed.
Here is SQL string failing.

PARAMETERS [forms]![Curr_date].[Beg_Date] Text ( 255 ),
[forms]![Curr_date].[End_Date] Text ( 255 );
SELECT dbo_pldb.end_date, dbo_pldb.acct3, dbo_pldb.subacct,
dbo_bondfile.adp, dbo_bondfile.desc1, dbo_pldb.pos_qty, ([realpl]+[pl]) AS
realpl1, dbo_pldb.fees
FROM dbo_pldb INNER JOIN dbo_bondfile ON dbo_pldb.cusip = dbo_bondfile.cusip
WHERE (((dbo_pldb.end_date) Between [forms]![Curr_date].[Beg_Date] And
[forms]![Curr_date].[End_Date]));

Please help. thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top