[john11670]

Hi - am having trouble trying to perform some calculations using SQL, querying a lotus notes database. The field TotalContractValueAUS is a numeric field.

This statement:

SQL = "SELECT CustomerName, SUM(TotalContractValueAUS) FROM Bids_Open_Bids_4_Export GROUP by CustomerName"

Returns this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Lotus][ODBC Lotus Notes]Incompatible data types in expression


Does anyone know a way around this? Are there any helpful websites you know of concerning SQL querying of a LN database?

[kickboxer]

Check that TotalContractValueAUS does have numeric values in it on each form that you are looking at and that any forms with no value in this field have a 0 in them. It sound to me like one of the forms has a text value in or a null value. You could create a view in the notes database to show the forms and have a column with the following formula in
@If(@IsNumber(TotalContractValueAUS); "OK"; "Not num")

sort the column so all the Not nums are together and you can see if any of the docs have this problem. If this is not the case, come back to me and I will think of something else