Complicated update

[Ashers]

I am helping my brother with his mileage claims database, for under 10K miles
you get one rate and another for over 10K. There was a simple update that
worked with one exception and that was the job that crossed over from one
rate to another.

I have written the code below, but Access doesn't like it and after a couple
of hours looking I can't see why. Any help will be greatly appreciated.

UPDATE [Cost Update 1] SET [Cost Update 1].Cost
=IIf(DSum(("[Miles]","Mileage")-[Miles])>=10000,([Miles]*0.25),IIf(DSum("[Miles]","Mileage")<10000,([Miles]*0.4)),(DSum("[Miles]","Mileage"
- 10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4));

 

[Michel Walsh]

It seems that the last argument must be:

0.4 * (10000 - DSUM() ) + 0.25*( miles - 10000 - DSum())

which is, after simplification:

0.15*10000 + 0.25* miles -0.15*DSum("miles", "mileage")


instead of the actual

(DSum("[Miles]","Mileage" -
10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4)
^
missing ) here, I think


Note that I haven't checked the ( and ) matches.




Vanderghast, Access MVP

 

[Ashers]

Almost,

but it is

0.25 * ( DSUM() - 10000 ) + 0.4*(miles - ( DSUM() - 10000))

If I explain, this previous total mileage =9500, this travel= 600.
So the claim would be
0.25 * (10100-10000) + 0.4 * (600-(10100-10000))
or 0.25 * 100 + 0.4 * 500


I hope this makes sense?

Ashley

It seems that the last argument must be:

0.4 * (10000 - DSUM() ) + 0.25*( miles - 10000 - DSum())

which is, after simplification:

0.15*10000 + 0.25* miles -0.15*DSum("miles", "mileage")


instead of the actual

(DSum("[Miles]","Mileage" -
10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4)
^
missing ) here, I think


Note that I haven't checked the ( and ) matches.




Vanderghast, Access MVP

I am helping my brother with his mileage claims database, for under 10K
miles
you get one rate and another for over 10K. There was a simple update that
worked with one exception and that was the job that crossed over from one
rate to another.

I have written the code below, but Access doesn't like it and after a
couple
of hours looking I can't see why. Any help will be greatly appreciated.

UPDATE [Cost Update 1] SET [Cost Update 1].Cost
=IIf(DSum(("[Miles]","Mileage")-[Miles])>=10000,([Miles]*0.25),IIf(DSum("[Miles]","Mileage")<10000,([Miles]*0.4)),(DSum("[Miles]","Mileage"
- 10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4));

 

[Michel Walsh]

Then just add the missing ) in:

DSum("[Miles]","Mileage" - 10000

since you cannot subtract 10000 from the string Mileage.


Vanderghast, Access MVP

Almost,

but it is

0.25 * ( DSUM() - 10000 ) + 0.4*(miles - ( DSUM() - 10000))

If I explain, this previous total mileage =9500, this travel= 600.
So the claim would be
0.25 * (10100-10000) + 0.4 * (600-(10100-10000))
or 0.25 * 100 + 0.4 * 500


I hope this makes sense?

Ashley

It seems that the last argument must be:

0.4 * (10000 - DSUM() ) + 0.25*( miles - 10000 - DSum())

which is, after simplification:

0.15*10000 + 0.25* miles -0.15*DSum("miles", "mileage")


instead of the actual

(DSum("[Miles]","Mileage" -
10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4)
^
missing ) here, I think


Note that I haven't checked the ( and ) matches.




Vanderghast, Access MVP

I am helping my brother with his mileage claims database, for under 10K
miles
you get one rate and another for over 10K. There was a simple update
that
worked with one exception and that was the job that crossed over from
one
rate to another.

I have written the code below, but Access doesn't like it and after a
couple
of hours looking I can't see why. Any help will be greatly appreciated.

UPDATE [Cost Update 1] SET [Cost Update 1].Cost
=IIf(DSum(("[Miles]","Mileage")-[Miles])>=10000,([Miles]*0.25),IIf(DSum("[Miles]","Mileage")<10000,([Miles]*0.4)),(DSum("[Miles]","Mileage"
- 10000)*0.25)+(([Miles]-(DSum("[Miles]","Mileage") - 10000))*0.4));