VB Data Report Designer 1

[TekMem]

Hi, I am I a new programmer. I have created a report using vb data report designer and Oracle data base. The thing, which is not working, is report function (SUM or AVG) . Table is using varchar2 as datatype.
I get an error message-,“Data type mismatch”.
-Re created a view using SQL to_number function to convert the data type still same error message.
-Data format property of the function is changed to number –still does not work.
When I created similar table with number field instead of varchar2. Above function works fine.
The problem with me is that I have to use varchar2 as datatype in my table and create the report.
I am stuck here….and your help will be appreciated.
Thanks

 

[garths2]

If there is any alpha character in the field you are trying to do a numeric function on in Oracle, it will report the error you are getting. You need to filter out any possibilities of getting an alpha character in your result set through the SQL statement, before you can put it through a numeric function such as to_number or SUM, AVG etc.

Hope that helps.

GS

 

[TekMem]

Thanks garths2 for your reply. Those are numbers, which I am getting from subtracting dates shown below.
to_Number(to_date(VARCHAR_255_12)-to_date(VARCHAR_255_14))).
I have no clue how to proceed, may be now you can help me.
Thanks.

 

[garths2]

Could you provide some sample dates from the VARCHAR_255_12 and VARCHAR_255_14 fields. Are there any NULL's in these fields? Could you also explain why a VARCHAR2 datatype is used on these fields instead of the DATE datatype?

Thanks,
GS

 

[garths2]

TekMem,

I had another thought, have you tried putting in the optional format specifier for the to_date function? i.e.

to_date(VARCHAR_255_12,'MM/DD/YYYY')
to_date(VARCHAR_255_14,'MM/DD/YYYY')

Are you using the Microsoft ADO Data Control as the dataSource for the VB dataReport?

GS