0

Access 2010: here are the conditions:

multiply with 1.5 if:
      monday - friday (08:00 - 16:00) 
      monday - friday (16:00 - 00:00) 
      satuday - sunday(08:00 - 16:00) 

multiply by 2 if:
      monday - friday (00:00 - 08:00)
      saturday- sunday (16:00 - 00:00)

multiply by 3 if:
      saturday - sunday (00:00 - 08:00)

And I made a funny long expression in expression builder (dont laugh, it works) :)

=IIf(([day]="monday" Or [day]="tuesday" Or [day]="wednesday" Or [day]="thursday" Or [day]="friday") And ([time_from]>=TimeValue("08:00") And [time_from]<=TimeValue("23:59") And ([time_until]>=TimeValue("08:00") And [time_until]<=TimeValue("23:59")));([time_until]-[time_from])*1,5;IIf(([day]="saturday" Or [day]="sunday") And ([time_from]>=TimeValue("08:00") And [time_from]<=TimeValue("16:00") And ([time_until]>=TimeValue("08:00") And [time_until]<=TimeValue("16:00")));([time_until]-[time_from])*1,5;IIf(([day]="monday" Or [day]="tuesday" Or [day]="wednesday" Or [day]="thursday" Or [day]="friday") And ([time_from]<TimeValue("08:00") And [time_until]<TimeValue("08:00"));([time_until]-[time_from])*2;IIf(([day]="saturday" Or [day]="sunday") And ([time_from]>=TimeValue("16:00") And [time_until]>=TimeValue("16:00"));([time_until]-[time_from])*2;IIf(([day]="saturday" Or [day]="sunday") And ([time_from]<=TimeValue("08:00") And [time_until]<=TimeValue("8:00"));([time_until]-[time_from])*3;"false")))))

there are many ifs, and it works... but how do I make a sum from all this?? I wish I could say Sum(name_of_the_calculated_field) but it doesnt work...

can you please help me with this? how to sum these results? or maybe you have another better idea... if yes, keep in mind that I am a beginner!

thanks

Volkan
  • 494
  • 3
  • 14
  • 32
  • Where is that expression used? Is it the definition of a calculated field which is part of your table's structure? Or is it a calculated field expression in a query? Or the Control Source property of a text box? Something else? – HansUp Jan 30 '15 at 03:22
  • it is a text field on a subform. Control Source on Data tab in properties. – Volkan Jan 30 '15 at 13:18

0 Answers0