IF Function Format -- Microsoft Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to pull data from SQL data table into Excel for pivot table.
Field_1 is a value; always positive. Field_2 is a text flag; either "C"
or"D". Depending on the flag in Field_2; Field_1 is either positive or
negative. What is the format of the entry in Microsoft Query so that a
Field_3 can accurately be calculated?

In Excel I would entry =if(Field_2="D",Field_1,-Field_1).
 
In Access, the function would be IIF(Field_2="D",Field_1,-Field_1)

Unfortunately I have no idea whether it will work with your SQL data.

If that fails, you can always to the calculation on the worksheet before you
do the pivot table.
 
The standard SQL syntax, which Jet (MS Access) does not support, is
CASE e.g. using pubs from MS Query:

SELECT
CASE title_id
WHEN 'BU1032' THEN 0 - royalty
ELSE royalty
END AS royalty_balanace
FROM pubs.dbo.roysched roysched
;

Jamie.

--
 
Back
Top