Formula in MSQuery, as opposed to in Excel (Excel 2002)

Topic

New Reply

Hi,

Is it possible to have the formula below in the msquery instead of having it calculate in Excel AFTER the data is downloaded into Excel…

For example, if G2 is equal to JUL08, then put the dollar amount in this new column, otherwise put 0. Below is the formula I have in Excel… I’d like to make the column in msquery instead. I have used formulas before in msquery like left(FILE NAME,4) and it works, but I can’t get an If, Then type formula to work. Is it even possible?

=IF($G2=$I$1,$F2,0)

Thanks!
Lana

Reply | Quote

Replies

You can use the IIf function instead if IF in queries:

IIf(Month='JUL08',Amount,0)

where Month is the name of the field you want to test on (corresponding to column G) and Amount is the currency field (corresponding to column F).

Reply | Quote

It accepts the formula now, however when I refresh the query it gives me this message:

SQL0104 – Token = was not valid. Valid tokens:),.

Here is the formula I typed in:

IIF(BPGDNB=’JAN08′,BPBAVA,0)

Reply | Quote

Do you have null values (blanks) in the BPGDNB field? If so, try

IIf(BPGDNB & ''='JAN08',BPBAVA,0)

(That shouldn’t really be necessary, but it’s worth a try)

Reply | Quote

Now it gives me this error message… the other error message said the = token is not valid, and now this one says the & token is not valid. Does this make sense? I would think we’d be able to use the = sign in a formula??

SQL0104 – Token & was not valid. Valid tokens:),.

Reply | Quote

I have tested the expression using one of my databases and it worked correctly, so I can’t help without seeing (stripped down copies of) the database and the workbook.

Reply | Quote