UDF SQL Statement help

[maw230]

I have created a UDF that uses an ODBC connection to return Sales Units based on a set of criteria: Line, Item#, BeginDate, EndDate, Price.

Currently it sums all SalesUnits within the given period based on the price. However, I would like for it to subtract SalesReturns in order to get a more accurate number and avoiding writing a new function that gets returns only (the sales one takes over an hour as is).

Code for current function:

Please Login or Register  to view this content.

I have tried replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and several variations of that with parentheses in different spots, but have had no luck. Can I achieve this calculation within one VBA function?

[Bob Phillips]

What is JDQTYS?

[maw230]

Sorry, that is Sales Units.
I am trying to get QTYS - QTYR (Units-Returns).

[Bob Phillips]

I mean is it a table column, a VBA variable, where does it come from?

[maw230]

It is a field that comes from DATAWHSE.DWPOSDATA.JDXMIT which is a table linked to Excel through ODBC.

[maw230]

Still trying to make a calculation within my VBA SQL statement.

[romperstomper]

Wouldn't it just be:

Please Login or Register  to view this content.

?

[snb]

Using the ODBC connection you can import all the relevant data for the calculation.
Instead of using a function that is importing line by line, reading line by line and writing line by line I think you'd better use a macro in the importing worksheet. Reading the relevant values into an array, performing the calculation in that array and writing into that array.
At the end you can write the array into the 'importworksheet'.
I fear it will improve the performance considerably.

Example:

Please Login or Register  to view this content.

[maw230]

romper,
I would think so, but I cannot get it to work this way so far. It just returns a blank cell.

snb,
Thanks for the suggestion. It looks a little steep for my skill level, but I would like to understand it as it could be able to help with the performance of many current functions that I use.

Reading the relevant values into an array

By relevant values do you mean only the values brought back by the criteria? The table I am pulling from is massive and slow, so I would have to only bring in the values I need.

I have never used an array before either, so your code is a little confusing to me..

[romperstomper]

Are both the fields numeric, and do they both contain data (rather than maybe being null)?

[maw230]

Yes, both fields are numbers. Quantity Sold minus Quantity Returned.

[romperstomper]

Is either field ever null? What is the database?

[maw230]

No, never nulls. There would be 0's.

What is the database?

Not sure I know what you mean. The table are stored on IBM AS/400 backend..

[romperstomper]

Have you checked to see if an error is being raised (you don't currently do anything in your error handler)?
I'm also not sure why you are returning a string here?