If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
iff statement did not work
Below is how I handle the IIf statement on the mainframe report writer. Can you help me convert this expression into Access. Do I have to create a Query to handle it, or can I do it all in the report as in the mainframe reportwriter. --- ----- ----------- These calculations are done prior to record read 5,00 1 3 LOACNT=0 5,00 2 3 EXTCNT=0 5,00 3 4 COLOA=0 5,00 4 4 COEXT=0 These calculations are done after record read 5,00 100 RECCNT=sum(1) 5,00 101 If emstcd='LOA' then LOACNT=loacnt+1 else LOACNT=LOACNT 5,00 102 If EMSTCD='EXT' then EXTCNT=EXTCNT+1 else EXTCNT=EXTCNT 5,00 103 If EMSTCD='LOA' then COLOA=COLOA+1 else COLOA=COLOA 5,00 104 If EMSTCD='EXT' then COEXT=COEXT+1 else COEXT=COEXT Above calcs are done in group footer. -- vickilynn |
#2
|
|||
|
|||
iff statement did not work
I either a group or report footer or header, you could use control sources
like: =Sum(Abs([EMSTCD]="LOA")) =Sum(Abs([EMSTCD]="EXT")) -- Duane Hookom MS Access MVP -- "Vicki Leatherberry" wrote in message ... Below is how I handle the IIf statement on the mainframe report writer. Can you help me convert this expression into Access. Do I have to create a Query to handle it, or can I do it all in the report as in the mainframe reportwriter. --- ----- ----------- These calculations are done prior to record read 5,00 1 3 LOACNT=0 5,00 2 3 EXTCNT=0 5,00 3 4 COLOA=0 5,00 4 4 COEXT=0 These calculations are done after record read 5,00 100 RECCNT=sum(1) 5,00 101 If emstcd='LOA' then LOACNT=loacnt+1 else LOACNT=LOACNT 5,00 102 If EMSTCD='EXT' then EXTCNT=EXTCNT+1 else EXTCNT=EXTCNT 5,00 103 If EMSTCD='LOA' then COLOA=COLOA+1 else COLOA=COLOA 5,00 104 If EMSTCD='EXT' then COEXT=COEXT+1 else COEXT=COEXT Above calcs are done in group footer. -- vickilynn |
#3
|
|||
|
|||
iff statement did not work
Thanks Duane, that worked with a lot fewer steps than the mainframe. What
would be the simplest way to sum those two fields. I tried =sum(abs([EMSTCD]="LOA")) + (abs([EMSTCD]="EXT")) above control only gave me the first total, not the sum of the two? -- vickilynn "Duane Hookom" wrote: I either a group or report footer or header, you could use control sources like: =Sum(Abs([EMSTCD]="LOA")) =Sum(Abs([EMSTCD]="EXT")) -- Duane Hookom MS Access MVP -- "Vicki Leatherberry" wrote in message ... Below is how I handle the IIf statement on the mainframe report writer. Can you help me convert this expression into Access. Do I have to create a Query to handle it, or can I do it all in the report as in the mainframe reportwriter. --- ----- ----------- These calculations are done prior to record read 5,00 1 3 LOACNT=0 5,00 2 3 EXTCNT=0 5,00 3 4 COLOA=0 5,00 4 4 COEXT=0 These calculations are done after record read 5,00 100 RECCNT=sum(1) 5,00 101 If emstcd='LOA' then LOACNT=loacnt+1 else LOACNT=LOACNT 5,00 102 If EMSTCD='EXT' then EXTCNT=EXTCNT+1 else EXTCNT=EXTCNT 5,00 103 If EMSTCD='LOA' then COLOA=COLOA+1 else COLOA=COLOA 5,00 104 If EMSTCD='EXT' then COEXT=COEXT+1 else COEXT=COEXT Above calcs are done in group footer. -- vickilynn |
#4
|
|||
|
|||
iff statement did not work
=sum(abs([EMSTCD]="LOA")) + Sum(abs([EMSTCD]="EXT"))
or possibly =sum(abs(Instr("EXT~LOA",[EMSTCD])0)) or =sum(abs([EMSTCD]="LOA" OR [EMSTCD]="EXT")) -- Duane Hookom MS Access MVP -- "Vicki Leatherberry" wrote in message ... Thanks Duane, that worked with a lot fewer steps than the mainframe. What would be the simplest way to sum those two fields. I tried =sum(abs([EMSTCD]="LOA")) + (abs([EMSTCD]="EXT")) above control only gave me the first total, not the sum of the two? -- vickilynn "Duane Hookom" wrote: I either a group or report footer or header, you could use control sources like: =Sum(Abs([EMSTCD]="LOA")) =Sum(Abs([EMSTCD]="EXT")) -- Duane Hookom MS Access MVP -- "Vicki Leatherberry" wrote in message ... Below is how I handle the IIf statement on the mainframe report writer. Can you help me convert this expression into Access. Do I have to create a Query to handle it, or can I do it all in the report as in the mainframe reportwriter. --- ----- ----------- These calculations are done prior to record read 5,00 1 3 LOACNT=0 5,00 2 3 EXTCNT=0 5,00 3 4 COLOA=0 5,00 4 4 COEXT=0 These calculations are done after record read 5,00 100 RECCNT=sum(1) 5,00 101 If emstcd='LOA' then LOACNT=loacnt+1 else LOACNT=LOACNT 5,00 102 If EMSTCD='EXT' then EXTCNT=EXTCNT+1 else EXTCNT=EXTCNT 5,00 103 If EMSTCD='LOA' then COLOA=COLOA+1 else COLOA=COLOA 5,00 104 If EMSTCD='EXT' then COEXT=COEXT+1 else COEXT=COEXT Above calcs are done in group footer. -- vickilynn |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Please Help?? Auto Update Field in table | A. Smart | General Discussion | 8 | February 2nd, 2006 01:19 PM |
Need help please with making this SELECT statement work!!!!!! | Mekinnik via AccessMonster.com | Running & Setting Up Queries | 5 | December 25th, 2005 01:41 AM |
Calendars in OL2003/OL2000 | Rich | General Discussion | 16 | February 10th, 2005 12:41 AM |
EXCELL SQL statement that won't work for ACCESS | jackle | General Discussion | 0 | January 8th, 2005 07:13 PM |
How to select a cell from another work sheet base on a formula | Jane | Worksheet Functions | 1 | August 7th, 2004 12:11 AM |