If..then statement

[naomi]

hi. i have a large if..then statement, but every time i try to add more arguments it starts acting weird

This is a 3 argument sample of the query I need to write below.

Can somebody please give me a more concise and accurate way of doing this query?

CntD: IIf((([AUDITS]![RECD_MAIL]="D") And ([AUDITS]![CALLER_STATUS]="D")),1,IIf((([AUDITS]![RECD_MAIL]="D") And ([AUDITS]![CALLER_STATUS] Like "")),1,IIf((([AUDITS]![CALLER_STATUS]="D") And ([AUDITS]![RECD_MAIL] Like "")),1,IIf((([AUDITS]![RECD_MAIL]="D") And ([AUDITS]![CALLER_STATUS] Not Like "")),0,0))))

 

[FoFa]

This kind of thing can get pretty nasty. have you concidered breaking it up? I usually (because of maintenance reasons) will create a work table (think of it like a temp table) and append the data in whatever selection I have. Than run multiple queries against the fields, and set aother (new) field in the work table. Then have a select query pull it out. If it is a shared DB you can use a userid or time stamp to control the current users rows.
Just a thought. BTW I think the query line is limited to string length (255) and if you go over that it errors or acts weird.
So in my work table I would have a new field called CntD and run multiple queries populating that with query conditions (instead of IIF). Then run a select query against the final results (then usually delete all my rows after that.)

 

[naomi]

Naomi Dodd - Reply to post 'If..then statement'

That would be ideal if 50 other people weren't using this report. As it stands, i need queries so after i finish this horrible report, i don't want to look at it again. so, i guess i will just have to set up 15 different columns full of iif statements. Is there any way i can do this faster in VBA code?????

 

[FoFa]

Gee, if you have 50 people using one Access DB, you have bigger problems than this query ;-)
But what I say still works. Of course I am assuming they run this from a form and not the access reports system screen.
Here is how I do it. Create a table to remain there that is your work table. Add your CntD field, and a UserID or date field as a key, I will use a date field in this example. When they Click a button (or whatever) to run the report, kick off a macro, or even a VB script that populates a invisible field in the form with the current time (your key for that user) and either runs the queries and the report (macro) or populates with a vbscript and then runs the report. Both use the time stamp in the form as the key for that users rows as does the report query. Benefits, once it is working, it usually works good, and it is easier to maintain when a change is required. Cons, takes longer to develop, runs a squish longer.

 

[naomi]

thanks... and my DB runs off a SQL server, so its not too bad....

 

[Pat Hartman]

You have made the IIf() more complicated than it needs to be.

CntD: IIf([AUDITS]![RECD_MAIL]="D" And ([AUDITS]![CALLER_STATUS]="D" OR ([AUDITS]![CALLER_STATUS] = "" OR IsNull(([AUDITS]![CALLER_STATUS])),1,0)

Since this is only one set of conditions, I would put the code in a function where I could use a Case statement which is much more readable.

Also, since your table is not an Access table, I would reference the function in the report rather than in the query. When you use language elements that are not directly translateable to the db server's SQL dialect, you force Jet to retrieve ALL rows from the server table rather than just the ones requested.

 

[FoFa]

If it is running on SqlServer, have you considered a Stored Procedure to do this instead?