ODBC -- Call Failed

Hi,

Check the length of the VarChar definition of the field in SQL.  Access only supports up to 255.

Regards,

Bill

mbizup...

that part of the question i have fixed....and now getting the above error.....

Bill

how do i check the lenght of the varchar....happy to post sql code..

ok i have found

CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],

Shouldn’t be a problem in string only when you pass it out to text field is that an issue???

is there a work around??

bill.....

Do you have access to SS Management Studio?

Are you storing a date value in a varcha(8) field?

You should change the SQL db to field to match the data stored.

Bill

Do you have access to SS Management Studio? - No

Are you storing a date value in a varcha(8) field? - is this fos_datetime_closed_op and open....as above in the access tbl......i have looked at the access tbl in desing view and the column that relates to

fos_datetime_closed_op,
fos_datetime_open_op,

are both blank...not data held...

You should change the SQL db to field to match the data stored. can we not restrict access to 250 char......cutt off the rest of theoutput?

If it's a varchar defined field and you are writing an update or insert statement then you can limit the string to 255 or less using

Trim(Left(yourString,255))

There may be additional issues when passing dates to-from Access and SQL.

When you pass date TO SQL and sve as VarChar then you should use CStr(yourDateField)

When you retrieve date FROM SQL into Access you should use CDate(yourSQLVarCharField)

This will maintain consistency.

CAST and CONVERT are not used in Access.

Regards,

Bill

Hi bill

This works on my colleavgue machine without the error.....Hmmmmmm!!

i tried the trim(left(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],255))


and is the above correct????

hi bill

am I correct as per previous messages

That will not work in Access.  CAST and CONVERT are SQL server T-SQL functions.

Where are you running this code?

Regards,

Bill

in access 2003...

Regards

via a pass through qry...

Hi,

OK.  If you're using a pass through query then you need to pass the information into the SQL statement in T-SQL format.  I would suggest you convert the values before you send to SQL - much easier in my opinion.

Please post the entire query SQL.

Thanks,

Bill

bill m- Apologies post wrong code....

Apolgies but code below:

SELECT 

snapshot_date, 
contact_id,
demand_type,
valid_case_ind,
date_in_bank_datetime,
CAST([date_in_bank_dateonly] AS DATETIME) AS [date_in_bank_dateonly],
date_in_cr_datetime,
CAST([date_in_cr_dateonly] AS DATETIME) AS [date_in_cr_dateonly],
case_created_datetime,
CAST([case_created_dateonly] AS DATETIME) AS [case_created_dateonly],
case_status,
Workstream,
product_01,
product_02,
product_03,
product_04,
cal_days_to_cr,
bus_days_to_cr,
cal_days_to_log,
bus_days_to_log,
case_created_hour,
CAST([init_dateonly_closed] AS DATETIME) AS [init_dateonly_closed],
init_tr_calls,
init_tr_contacts,
init_tr_calls_ind,
init_cal_days_os_bank,
init_bus_days_os_bank,
init_cal_days_os_cr,
init_bus_days_os_cr,
init_cal_days_os_created,
init_bus_days_os_created,
reopen_datetime,
CAST([reopen_dateonly] AS DATETIME) AS [reopen_dateonly],
reopen_hour,
reopen_und_calls,
reopen_und_contacts,
reopen_und_calls_ind,
reopen_und_contacts_ind,
reopen_cla_calls,
reopen_cla_contacts,
reopen_cla_calls_ind,
reopen_cla_contacts_ind,
reopen_cal_e2e_bank_closed,
reopen_cal_e2e_cr_closed,
reopen_bus_e2e_bank_closed,
reopen_bus_e2e_cr_closed,
reopen_cal_e2e_created_closed,
reopen_bus_e2e_created_closed,
paf_outcome,
paf_final,
paf_date,
CAST([ack_breach_dateonly] AS DATETIME) AS [ack_breach_dateonly],
ack_breach,
ack_breach_backdated,
CAST([four_week_breach_dateonly] AS DATETIME) AS [four_week_breach_dateonly],
four_week_breach,
four_week_breach_backdated,
CAST([eight_week_breach_dateonly] AS DATETIME) AS [eight_week_breach_dateonly],
eight_week_breach,
eight_week_breach_backdated,
CAST([ack_potential_dateonly] AS DATETIME) AS [ack_potential_dateonly],
CAST([four_week_potential_dateonly] AS DATETIME) AS [four_week_potential_dateonly],
CAST([eight_week_potential_dateonly] AS DATETIME) AS [eight_week_potential_dateonly],
qc_first_decision,
qc_first_decision_rating,
qc_first_decision_area,
qa_first_decision_rating,
qa_first_decision_area,
qa_first_decision_overturned,
qc_second_decision_overturned,
qa_second_decision,
qa_second_decision_overturned,
CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],
fos_dateonly_closed_omb,
fos_cal_days_to_close_op,
fos_cal_days_to_close_omb,
fos_bus_days_to_close_op,
fos_bus_days_to_close_omb,
fos_os_cal_day_op,
fos_os_cal_day_omb,
fos_os_bus_day_op,
Last_Updated,
original_group,
current_group,
Contact_Reference,
Status_Updated,
week_breach_4,
Breach_8_Date,
CAST([Breach_Ack_Dateonly] AS DATETIME) AS [Breach_Ack_Dateonly],
CAST([Breach_Area_Dateonly] AS DATETIME) AS [Breach_Area_Dateonly],
breached_1,
closed_non_reportable,
CR_Action_Taken,
cr_summary,
cust_req_amount,
date_handedin,
date_fos_closed,
executive_name,
Final_Desc_01,
Final_Desc_02,
FOS_Invoice,
Handed_In_From,
High_Profile,
Hot_Topic

FROM  tbl

Select allOpen in new window

HI,

Change CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112)

to

CAST([fos_datetime_new_op], 112)

The CAST statement should return yyyymmdd if fos_datetime_new_op is a valid date.

There should be no need to change to VarChar(8).

Regards,

Bill

Bill

I am guessing that [fos_datetime_new-op] is not a vaild date as now i am getting the followinf error:

Incorrect syntax near 'CAST' expected 'AS'. (#1035)

Sounds like a data problem.  Please check the data in the [fos_datetime_new_op] field.

SELECT [fos_datetime_new_op]  FROM tbl

What is the data in this field?

Bill

Bill

the result is 21/11/2011 15:30:30

i also ran

QRY:
Select
Count (*)
,Sum(isdate([fos_datetime_new_op]))

FROM tbl

Results:

Expr 1000                   Expr 1001
4781                            127

Hence out of the 4781 records in the above table only 127 are recongnized as date/time by the function ISDATE() and the rest as NULL

bill

can we amend the sql to to ignore null values....

See if TRY_CAST([fos_datetime_new_op], 112) works - this is a SQL Server 2012 version option.

If not use ISNULL ( check_expression , replacement_value )

... (ISNULL([fos_datetime_new_op],NULL, CAST([fos_datetime_new_op], 112))) AS ...

Regards,

Bill

Thanks bill

what would the complete statement be


... (ISNULL([fos_datetime_new_op],NULL, CAST([fos_datetime_new_op], 112))) AS ...