use if statement in excel's microsoft query

[sue1127]

I am retrieving data through microsoft query from a sybase database. There is one column that could have a "V" or a "C". I would like to retrieve the record regardless of whether it has a C or V in the column, but I want to display the contents of that column only if it is a V. If it's a C, I want to display a space instead.

Is this possible in Microsoft query?

Thanks,

Sue

 

[ETID]

I've added a col. to the sheet after the data is returned.
and named the col. something similar. then put a conditionl formula in that col. to modify the data.

in the properties for the sybase query (right click in the returned data), check fill down formulas automatically.

then.....

(assuming C or V return to Col A)

=if(A1="C"," ",A1)

then fill down.


then when your query refreshes, it will fill or remove the formulas to match the number of records.

 

[sue1127]

ETID,

Thanks for responding. I actually have done it the way you suggest, but I'm trying to find a way to automatically limit to V. Some people in our AP office will be doing this every week. They will be going into Microsoft Query and changing the date, then they will bring the data back to excel and save as .prn to transmit to the bank. I thought if there were a way to do it in MS Query, they wouldn't have to do the IF statement every time.

Thanks,

Sue

 

[ETID]

can you do the query in access then call the query from excel?

that way you can create an expression that places a space in place of C.

then call the expression field into excel rather than the field that has the V's and C's

 

[rnuage]

Sue,

I work with Oracle and am not sure if Sybase supports this, but you can do it in the select phrase with a decode statement, e.g.:
select decode(column_name,'C',' ',column_name)

This expression will return a space if the value of the column is "C" and return the value in the column for all other cases.

Ron

 

[SkipVought]

In your QBE grid for that column use

Like "*C*" Or Like "*V*"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sue1127]

Thanks to everyone for responding. I've been involved in fixing a database disaster caused by someone's not having access to all necessary tables to update, so I haven't had a chance to get back to this problem.

As it turns out, the problem has taken a somewhat different twist. The bank doesn't seem to be able to read the .prn correctly when there is sometimes a V in the last position and sometimes not. I tried putting in a space by pressing the space bar in Excel, but that didn't seem to help. Shouldn't a space in Excel register as a blank character(ascii 32)? Would that not get saved when the file was saved as space delimited? Or is the bank not doing something it needs to do?

Thanks again,

Sue