Can Microsoft Query create formula or must VBA be used? 1

[makeitwork09]

I apologize in advance if this the incorrect forum.
I am using Microsoft 2003 for this task.

I have a worksheet that has a result set from a SQL statement using the Microsoft Query feature. There are also formulas in the same worksheet. The problem is, each time the query is refresed, the dataset has decreased and (1) the formula for the last row remains at the original location and (2) the rows above it read as #REF. Note this is only for the rows between the second to last row of the new dataset and what should be the last row.

I decided that perhaps it would be a could idea to create the formula in the SQL statement to avoid this, but from what I've done so far, this does not seem like something that can be done without the use of VBA. Please see below and let me know if my assumption is correct.

 select l.loan, l.pool,
      '{=ADDRESS(MATCH("'+ rtrim(l.loan)+'"&"'+
rtrim(l.pool)+'",loan&pool,0)+1,1,4)}' as [combinations]
from lms.dbo.nsfp_loanmast l
left outer join lms.dbo.sfp_balances b on
    l.pool = b.pool and
    l.loan = b.loan
where b.prin_bal != 0 and
     b.hist_date = (select max(hist_date) from nyl_sfp_balances)
order by l.pool,l.loan

The {} is me trying to simulate CTRL-SHIFT-ENTER. Dumb, idea, I know.

 

[SkipVought]

Hi,

Select in the resultset (QueryTable)

Right-click > Data Range Properties

In the External Data Range Properties window, at the VERY BOTTOM is a check box that you'll find intriguing. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Skip you are AWESOME!!! I don't even have to do what I was trying to do above now.

I am adding this to my list of good things to know.

Thanks

 

[SkipVought]

So...

I gather that you WERE intrigued.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

OMG was I ever.

 

[SkipVought]

BTW, another little known feature of Excel, that does the same sort of thing, as far as propogating formulas to the next new row of data, as you MANUALLY add data to a table (as opposed to the MS Query) is...
[tt]
Data > List > Create List
[/tt]
You will never have to add dummy rows for your formulas that tend to mess things up very often.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Wow!! Ok thanks for that tip too!

Little things make me happy. Especailly given the day I'm having.