Expressions Editions Nightmare

[rafal444]

I am writing queries with some complex IIF statements. I find it very difficult to edit and read those Expressions in Access Editor.

Is there any other/better Editor I can use to edit those statements? Here is a sample:

Domestic HV Distribution Expenses Calculated: IIf(IsNull([Total Distribution Expenses]),IIf(IsNull([Domestic Distribution Expenses]),Nulltozero([Domestic HV Distribution Expenses]),([Domestic Distribution Expenses])*IIf(NullToZero([Domestic Revenues])=0,0,NullToZero([Domestic HV Gross Revenue])/[Domestic Revenues])),IIf([Dom HV Deals].[HVDeal]="Royalty",DMax("[Edited Expenses]","[Imputed HV Expenses]","[Gross Revenues] <= " & NullToZero([Domestic Distribution Expenses])),([Total Distribution Expenses])*(IIf(NullToZero([Total revenues])=0,0,NullToZero([Domestic HV Gross Revenue])/[Total Revenues]))))

 

[Pampers]

Maybe this helps,
Sometimes I use notepad to give the code a simple layout to keep track of the number of nestings and brackets, then paste it into the access expression builder, like:

IIF(variable = ??,expression,
IIF(variable2 = ??, expression,

and so on..
then paste it into the expression builder of Access (who will elimate the formatting), if you have an error correct in the notepad-version, and try again.


Pampers
Just back from holiday...
that may it explain it.

 

[rafal444]

Yes, you are right. It just would be nice to have it in Accesss...

 

[TheAceMan1]

How are ya rafal444 . . .

When an if statement starts getting this complex I'll return the value thru a function in VBA using an [blue]If...Then...Else[/blue] Statement.

[blue]Domestic HV Distribution Expenses Calculated:FunctionName()[/blue]

Is certainable more readable as well as the [blue]If...Then...Else[/blue] Statement in VBA . . . ;-)

See Ya! . . . . . .

 

[rafal444]

I am moving stuff to VBa code, how can I reference other query fields in VBA code? Here is an example:

Domestic HV Distribution Expenses Calculated: IIf(IsNull([Total Distribution Expenses]),IIf(IsNull([Domestic Distribution Expenses]),Nulltozero([Domestic HV Distribution Expenses]),([Domestic Distribution Expenses])*IIf(NullToZero([Domestic Revenues])=0,0,NullToZero([Domestic HV Gross Revenue])/[Domestic Revenues])),IIf([Dom HV Deals].[HVDeal]="Royalty",DMax("[Edited Expenses]","[Imputed HV Expenses]","[Gross Revenues] <= " & NullToZero([Domestic Distribution Expenses])),([Total Distribution Expenses])*(IIf(NullToZero([Total revenues])=0,0,NullToZero([Domestic HV Gross Revenue])/[Total Revenues]))))

How would you write this in VBA?

 

[TheAceMan1]

rafal444 . . .

Setup the function with arguements to receive the query fields:

[blue]Public Function FunctionName(DomesticHVDistributionExpensesCalculated, TotalDistributionExpenses)
   [green]your code here[/green]
End Function[/blue]

The custom field in the query would look like:

[blue]Domestic HV Distribution Expenses Calculated:FunctionName([Domestic HV Distribution Expenses Calculated], [Total Distribution Expenses])[/blue]

[purple]You also have a good example of how a long naming convention can make code hard to read![/purple]

See Ya! . . . . . .