ODBC -- Call Failed

Hi Experts

i am getting the following error message when runniung a pass thorugh qry:

{micorsoft][ODBCSQL Server Driver][SQL Server] the connection of a varchar data type to a determine date type resulted in an out-of-range value. (#242)

How do i fix this.....
route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossProgrammerCommented:
Hi,

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

Regards,

Bill
0
route217JuniorAuthor Commented:
mbizup...

that part of the question i have fixed....and now getting the above error.....
0
route217JuniorAuthor Commented:
Bill

how do i check the lenght of the varchar....happy to post sql code..
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

route217JuniorAuthor Commented:
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.....
0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
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?
0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
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????
0
route217JuniorAuthor Commented:
hi bill

am I correct as per previous messages
0
Bill RossProgrammerCommented:
That will not work in Access.  CAST and CONVERT are SQL server T-SQL functions.

Where are you running this code?

Regards,

Bill
0
route217JuniorAuthor Commented:
in access 2003...

Regards
0
route217JuniorAuthor Commented:
via a pass through qry...
0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
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

Open in new window

0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
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)
0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
Bill

the result is 21/11/2011 15:30:30
0
route217JuniorAuthor Commented:
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
0
route217JuniorAuthor Commented:
bill

can we amend the sql to to ignore null values....
0
Bill RossProgrammerCommented:
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
0
route217JuniorAuthor Commented:
Thanks bill

what would the complete statement be


... (ISNULL([fos_datetime_new_op],NULL, CAST([fos_datetime_new_op], 112))) AS ...
0
Bill RossProgrammerCommented:
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,
(ISNULL([fos_datetime_new_op],NULL, CAST([fos_datetime_new_op], 112))) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
(ISNULL([fos_datetime_closed_op],NULL, CAST([fos_datetime_closed_op], 112))) 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.