If expression in Query -- is this possible?

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

Guest

Hello,

I have this query i designed in the query design grid. THe problem occurs
where i have the If statement.

INSERT INTO tblBatchAudit ( change, OCDescription, tblPriBand_ID, tblCmd_ID,
tblObjClass_ID, tblFunction_ID, tblPrgrm_ID, tblAcct_ID, tblCIN_ID,
tblAudit_ID, rmks )
SELECT [enter % change as decimal]*[funded] AS change,
qryOCSums.OCDescription, qryOCSums.tblPriBand_ID, qryOCSums.tblCmd_ID,
qryOCSums.tblObjClass_ID, qryOCSums.tblFunction_ID, qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID, qryOCSums.tblCIN_ID, if([change]>0,5,6) AS tblAudit_ID,
[Enter Audit Remarks] AS rmks
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));


I'm trying to get the tblAudit_Id field to automatically input a 5 if the
user enters a positive number for [change] or a 6 if the user enters a
negative number.

thanks
Laura
 
laura reid said:
Hello,

I have this query i designed in the query design grid. THe problem occurs
where i have the If statement.

INSERT INTO tblBatchAudit ( change, OCDescription, tblPriBand_ID, tblCmd_ID,
tblObjClass_ID, tblFunction_ID, tblPrgrm_ID, tblAcct_ID, tblCIN_ID,
tblAudit_ID, rmks )
SELECT [enter % change as decimal]*[funded] AS change,
qryOCSums.OCDescription, qryOCSums.tblPriBand_ID, qryOCSums.tblCmd_ID,
qryOCSums.tblObjClass_ID, qryOCSums.tblFunction_ID, qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID, qryOCSums.tblCIN_ID, if([change]>0,5,6) AS tblAudit_ID,
[Enter Audit Remarks] AS rmks
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));


I'm trying to get the tblAudit_Id field to automatically input a 5 if the
user enters a positive number for [change] or a 6 if the user enters a
negative number.

thanks
Laura
Click to expand...
 
You need to use I I F (that is IIF) instead of IF


IIf([change]>0,5,6) AS tblAudit_ID,
 
I do not believe that you can use the newly created field {change] in the
IIF function, it does not have a value when the SQL is being evaluated.

I do believe that you can use IIF([enter % change as decimal]*[funded] , ...
instead, and that Access will only ask you for the value of that parameter
once.

apologies if I am mistaken.

John Spencer said:
You need to use I I F (that is IIF) instead of IF


IIf([change]>0,5,6) AS tblAudit_ID,

laura reid said:
Hello,

I have this query i designed in the query design grid. THe problem
occurs
where i have the If statement.

INSERT INTO tblBatchAudit ( change, OCDescription, tblPriBand_ID,
tblCmd_ID,
tblObjClass_ID, tblFunction_ID, tblPrgrm_ID, tblAcct_ID, tblCIN_ID,
tblAudit_ID, rmks )
SELECT [enter % change as decimal]*[funded] AS change,
qryOCSums.OCDescription, qryOCSums.tblPriBand_ID, qryOCSums.tblCmd_ID,
qryOCSums.tblObjClass_ID, qryOCSums.tblFunction_ID,
qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID, qryOCSums.tblCIN_ID, if([change]>0,5,6) AS
tblAudit_ID,
[Enter Audit Remarks] AS rmks
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));


I'm trying to get the tblAudit_Id field to automatically input a 5 if the
user enters a positive number for [change] or a 6 if the user enters a
negative number.

thanks
Laura
Click to expand...
Click to expand...
 
Good catch, I missed the Alias in the earlier portion. I thought that
[Change] was a simple parameter prompt.


David F Cox said:
I do not believe that you can use the newly created field {change] in the
IIF function, it does not have a value when the SQL is being evaluated.

I do believe that you can use IIF([enter % change as decimal]*[funded] ,
...
instead, and that Access will only ask you for the value of that parameter
once.

apologies if I am mistaken.

John Spencer said:
You need to use I I F (that is IIF) instead of IF


IIf([change]>0,5,6) AS tblAudit_ID,

laura reid said:
Hello,

I have this query i designed in the query design grid. THe problem
occurs
where i have the If statement.

INSERT INTO tblBatchAudit ( change, OCDescription, tblPriBand_ID,
tblCmd_ID,
tblObjClass_ID, tblFunction_ID, tblPrgrm_ID, tblAcct_ID, tblCIN_ID,
tblAudit_ID, rmks )
SELECT [enter % change as decimal]*[funded] AS change,
qryOCSums.OCDescription, qryOCSums.tblPriBand_ID, qryOCSums.tblCmd_ID,
qryOCSums.tblObjClass_ID, qryOCSums.tblFunction_ID,
qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID, qryOCSums.tblCIN_ID, if([change]>0,5,6) AS
tblAudit_ID,
[Enter Audit Remarks] AS rmks
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));


I'm trying to get the tblAudit_Id field to automatically input a 5 if
the
user enters a positive number for [change] or a 6 if the user enters a
negative number.

thanks
Laura
Click to expand...
Click to expand...
Click to expand...
 
it works as is. I'm not having problems because it asks me for the [change]
before it does the iif statement in the audit field But I'll try it your way
too just to be sure.

Thanks
Laura

John Spencer said:
Good catch, I missed the Alias in the earlier portion. I thought that
[Change] was a simple parameter prompt.


David F Cox said:
I do not believe that you can use the newly created field {change] in the
IIF function, it does not have a value when the SQL is being evaluated.

I do believe that you can use IIF([enter % change as decimal]*[funded] ,
...
instead, and that Access will only ask you for the value of that parameter
once.

apologies if I am mistaken.

John Spencer said:
You need to use I I F (that is IIF) instead of IF


IIf([change]>0,5,6) AS tblAudit_ID,

Hello,

I have this query i designed in the query design grid. THe problem
occurs
where i have the If statement.

INSERT INTO tblBatchAudit ( change, OCDescription, tblPriBand_ID,
tblCmd_ID,
tblObjClass_ID, tblFunction_ID, tblPrgrm_ID, tblAcct_ID, tblCIN_ID,
tblAudit_ID, rmks )
SELECT [enter % change as decimal]*[funded] AS change,
qryOCSums.OCDescription, qryOCSums.tblPriBand_ID, qryOCSums.tblCmd_ID,
qryOCSums.tblObjClass_ID, qryOCSums.tblFunction_ID,
qryOCSums.tblPrgrm_ID,
qryOCSums.tblAcct_ID, qryOCSums.tblCIN_ID, if([change]>0,5,6) AS
tblAudit_ID,
[Enter Audit Remarks] AS rmks
FROM qryOCSums
WHERE (((qryOCSums.OCCode)=[enter EOR]));


I'm trying to get the tblAudit_Id field to automatically input a 5 if
the
user enters a positive number for [change] or a 6 if the user enters a
negative number.

thanks
Laura
Click to expand...
Click to expand...
Click to expand...
 
Back
Top