MS query calculated field

[Jubert]

I'm not familiar with MS query but I have managed to get the data imported (from our accounting system) for what I require with one exception. The number convention in the tables means that all lines are positive. I can correct this in excel after I import the data but my preference would be to add another column to the query which corrects the sign.

The criteria would be as follows:
if {CST_DETAIL.CT_SORTTYPE} in ['SLINV', 'SLADR', 'NLVJLC', 'PLCRN', 'PLADR', 'NLJNLC']
then {CST_DETAIL.CT_NETT}
else -{CST_DETAIL.CT_NETT}

The current SQL expression is below.
Can anyone give me a step by step as to how I can add a new field which corrects the CT_NETT output?
Thanks

SELECT ARM_GP_GROUPINGS.GrpCode, arm_cc_group_names.cc_name, CST_COSTCENTRE.CC_CODE, CST_COSTCENTRE.CC_NAME, CST_COSTCENTRE.CC_LEVELPOINTER, CST_COSTHEADER.CH_CODE, CST_COSTHEADER.CH_NAME, CST_COSTHEADER.CH_USER3, CST_COSTHEADER.CH_USER4, CST_DETAIL.CT_NETT, CST_DETAIL.CT_SORTTYPE, CST_DETAIL.CT_PERIODNUMBR, CST_DETAIL.CT_YEAR, SL_ACCOUNTS.CUCODE, SL_ACCOUNTS.CUNAME, CST_COSTHEADER.CH_CODE
FROM GSLIVE.dbo.arm_cc_group_names arm_cc_group_names, GSLIVE.dbo.ARM_GP_GROUPINGS ARM_GP_GROUPINGS, GSLIVE.dbo.CST_COSTCENTRE CST_COSTCENTRE, GSLIVE.dbo.CST_COSTHEADER CST_COSTHEADER, GSLIVE.dbo.CST_DETAIL CST_DETAIL, GSLIVE.dbo.SL_ACCOUNTS SL_ACCOUNTS
WHERE CST_DETAIL.CT_COSTCENTRE = CST_COSTCENTRE.CC_CODE AND CST_COSTCENTRE.CC_COPYHEADER = CST_DETAIL.CT_COSTHEADER AND CST_COSTHEADER.CH_CODE = CST_COSTCENTRE.CC_COPYHEADER AND CST_COSTCENTRE.CC_LEVELPOINTER = arm_cc_group_names.cc_levelpointer AND CST_COSTHEADER.CH_ACCOUNT = SL_ACCOUNTS.CUCODE AND ARM_GP_GROUPINGS.ch_code = CST_COSTHEADER.CH_CODE AND ((CST_DETAIL.CT_YEAR='C') AND (CST_DETAIL.CT_PERIODNUMBR=1))

 

[wideboydixon]

Stab in the dark but I think IIF is supported in MS Query (it's been a while ...). Try selecting something like this:

IIF(CST_DETAIL.CT_SORTTYPE="SLINV", -CST_DETAIL.CT_NETT, IIF(CST_DETAIL.CT_SORTTYPE="SLADR", -CST_DETAIL.CT_NETT, CST_DETAIL.CT_NETT)) AS CT_NETT,

If that works then you can extend to the other values.

WBD

 

[Jubert]

Hi WBD
Sorry you are dealing with such a novice.

I copy and pasted your suggestion into a new column header however it gave me a Syntax error.

I entered it without the AS CT_NETT, at the end (as my reading of the statement as an IF statement didn't know why it was there - however that may be erroneous logic). It then returned an error which said invalid Column name "SLINV", "SLADR". Statement(s) could not be processed.

I feel like its close....