Report - expression too long

Question

In a report, I had a field expression like so: (VraiFaux is French for IIf)

=VraiFaux(Somme([Nombre_Km]*54)/100+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc])<15 AND Somme([Nombre_Km]*54)/100+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc])>0

,15,Somme([Nombre_Km]*54/100)+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc]))

Basically, if the calculated value was less than 15, then make it 15, else put in the calculated value.

This worked fine, until I realized that I didn't want 0 to be rounded up to 15.  When I tried to correct for this by changing the expression to

=VraiFaux(Somme([Nombre_Km]*54)/100+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc])<15 AND Somme([Nombre_Km]*54)/100+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc])>0

,15,Somme([Nombre_Km]*54/100)+Somme([Montant_fixe]*54/47)+Somme([Montant_stationnement_etc]))

I got an error about how this was too long.  I am not an Access expert, this is in the report field property sheet.  I am not sure how to change my report so this does what I want.  There is a bit of code in my project.

Answer 1

There is a "View Code" button in the Tools section on my ribbon, and when I press that, A MS VBA window opens with a window in front that says Option Compare Database.  

Not sure what to do next.

Answer 2

I'm going to try to fix this right now.  Just finished fixing another little bug related to rounding Currency arithmetic.  

My problem is that this is happening in a report, and I am not sure how to connect reports to defined functions.  Clues?  I'm going to snoop inside my project while waiting for comments on how to do this exactly.  

Appreciate your patience with this newbie.

Answer 3

I agree with Duane here. User Defined Functions have the additional advantage of being easier to debug.

Answer 4

I think this expression should be about the same as yours however I'm not a fan of hard-code numbers like this into expressions. Typically the numbers 54 and 47 will change some day. I would hate to have to search for every place I used them. Minimally, I would create a user-defined function that I could use arguments like: Somme([Nombre_Km]), Somme([Montant_fixe]), and Somme([Montant_stationnement_etc]). The function would contain 54 and 47 to perform you business logic.

=VraiFaux(Somme([Nombre_Km])*0.54+Somme([Montant_fixe])*54/47+

  Somme([Montant_stationnement_etc])<15 AND 

  Somme([Nombre_Km])*0.54+Somme([Montant_fixe])*54/47+

  Somme([Montant_stationnement_etc])>0

  ,15,Somme([Nombre_Km])*0.54+Somme([Montant_fixe])*54/47+Somme([Montant_stationnement_etc]))

Answer 5

Try creating shorter aliases for your fieldnames. E.g. in your query specify

NK: [Nombre_Km]

and

MS: [Montant_stationnement_etc]

and so on, and use these shorter names in your IIF.