calculate in Excel query

[slackboy]

Im extracting data from Sage L500 using excel and query.

My order table holds credit notes as a positive value, it just marks them as 'CN'xxxxxx

So what I want is, val*-1 if Order_no begins with 'CN' else val

Anyone know if this can be done via msquery criteria??

Cheers

Slacky

 

[xlbo]

If you are pulling the data back into excel, it may actually be better to do the calc there

Add the formula to the right side of your data set and copy down

Right click in the data and choose Data Range Properties

Tick the box that says "Fill down formulae" and Bob's your mother's brother

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Slacky,

iif(left(val,4)="'CN'",right(val,len(val)-4)*-1,val)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[slackboy]

thx 4 replies

Geoff yeah that's fine, should thought simple!!!!
I'll go with that.

Skip, sorry not a programmer myself, Im sure yr code is great...but I wouldnt know what to do with it...lol

cheers anyway!

Slacky

 

[SkipVought]

That formula goes into the query instead of val

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244