dlookup problem

[rastolk]

Hi there

think my statements are too complex.


I created a Tblboatmen with 3 fields ID Length and Tarriff
however first record starts with 50mtr and the last record ends 400mtr
The problem is the records don't jump upwards with the same amount
meters
a third party created this tarriff which i have to use.
ID Length Tarriff
1 50 mtr $100
2 75 mtr $120
3 80 mtr $150
4 90 mtr $170
5 95 mtr $175
aso
aso

From a tabel [Ship] the field LOA(LengthOverAll) is filled with the

length of that ship
for example 87 mtr
I use in a report following unbound label named [shiplength]
=IIf([LOA]=<50;"50";IIF([LOA]=>51and [loa]=<75;"75";IIF([LOA]=>80
and[LOA]=<90;"90";aso
aso aso aso if there are too many IIf Access warns "this
is to complex"

The result = 90

To pick the right record in the TabelBoatmen a new unbound label with
=Dlookup("Tarriff";"TblBoatmen";"[length]="&[shiplength]) is created
which gives $ 170

Think above is very complex
Who can clear this for me?

Thanks in advance
Ron

 

[Guest]

Hi rastolk,
I though your question has been answered, but it hasn't.
Try using Switch function instead of IIf.
For example:
=Switch([LOA]<=50,"50",[LOA]<=75,"75",[LOA]<=80,"80",[LOA]>=80 And
[LOA]<=90,"90",[LOA]<=95,"95",[LOA]<=100,"100",[LOA]<=110,"110",aso,aso...)
I haven't check how many arguments could be used with the Switch function -
Check it yourseft.
Hey ! If you use ";" as List Seperator instead of ",", change the "," to ";".
Good luck.

Hi there

think my statements are too complex.


I created a Tblboatmen with 3 fields ID Length and Tarriff
however first record starts with 50mtr and the last record ends 400mtr
The problem is the records don't jump upwards with the same amount
meters
a third party created this tarriff which i have to use.
ID Length Tarriff
1 50 mtr $100
2 75 mtr $120
3 80 mtr $150
4 90 mtr $170
5 95 mtr $175
aso
aso

From a tabel [Ship] the field LOA(LengthOverAll) is filled with the

length of that ship
for example 87 mtr
I use in a report following unbound label named [shiplength]
=IIf([LOA]=<50;"50";IIF([LOA]=>51and [loa]=<75;"75";IIF([LOA]=>80
and[LOA]=<90;"90";aso
aso aso aso if there are too many IIf Access warns "this
is to complex"

The result = 90

To pick the right record in the TabelBoatmen a new unbound label with
=Dlookup("Tarriff";"TblBoatmen";"[length]="&[shiplength]) is created
which gives $ 170

Think above is very complex
Who can clear this for me?

Thanks in advance
Ron

 

[rastolk]

Hi Khoa

Yes that will do, didn't know Switch
it is much easier

You wrote
[LOA]>=80 And [LOA]<=90

has this effect on the result or was it mistyping ?


Thanks for reply

Best regards
Ron



Khoa schreef:

Hi rastolk,
I though your question has been answered, but it hasn't.
Try using Switch function instead of IIf.
For example:
=Switch([LOA]<=50,"50",[LOA]<=75,"75",[LOA]<=80,"80",[LOA]>=80 And
[LOA]<=,"90",[LOA]<=95,"95",[LOA]<=100,"100",[LOA]<=110,"110",aso,aso...)
I haven't check how many arguments could be used with the Switch function -
Check it yourseft.
Hey ! If you use ";" as List Seperator instead of ",", change the "," to ";".
Good luck.

Hi there

think my statements are too complex.


I created a Tblboatmen with 3 fields ID Length and Tarriff
however first record starts with 50mtr and the last record ends 400mtr
The problem is the records don't jump upwards with the same amount
meters
a third party created this tarriff which i have to use.
ID Length Tarriff
1 50 mtr $100
2 75 mtr $120
3 80 mtr $150
4 90 mtr $170
5 95 mtr $175
aso
aso

From a tabel [Ship] the field LOA(LengthOverAll) is filled with the

length of that ship
for example 87 mtr
I use in a report following unbound label named [shiplength]
=IIf([LOA]=<50;"50";IIF([LOA]=>51and [loa]=<75;"75";IIF([LOA]=>80
and[LOA]=<90;"90";aso
aso aso aso if there are too many IIf Access warns "this
is to complex"

The result = 90

To pick the right record in the TabelBoatmen a new unbound label with
=Dlookup("Tarriff";"TblBoatmen";"[length]="&[shiplength]) is created
which gives $ 170

Think above is very complex
Who can clear this for me?

Thanks in advance
Ron

 

[hennie]

*Hi there

think my statements are too complex.


I created a Tblboatmen with 3 fields ID Length and Tarriff
however first record starts with 50mtr and the last record ends
400mtr
The problem is the records don't jump upwards with the same amount
meters
a third party created this tarriff which i have to use.
ID Length Tarriff
1 50 mtr $100
2 75 mtr $120
3 80 mtr $150
4 90 mtr $170
5 95 mtr $175
aso
aso

From a tabel [Ship] the field LOA(LengthOverAll) is filled with

the
length of that ship
for example 87 mtr
I use in a report following unbound label named [shiplength]
=IIf([LOA]=<50;"50";IIF([LOA]=>51and [loa]=<75;"75";IIF([LOA]=>80
and[LOA]=<90;"90";aso
aso aso aso if there are too many IIf Access warns
"this
is to complex"

The result = 90

To pick the right record in the TabelBoatmen a new unbound label
with
=Dlookup("Tarriff";"TblBoatmen";"[length]="&[shiplength]) is created
which gives $ 170

Think above is very complex
Who can clear this for me?

Thanks in advance
Ron *

 

[Guest]

Glad to know you did it. The Switch function take the first TRUE condition
(from Left to Right order), and return it's argument, and then "Exit". Then,
for example, if we type:
=Switch([LOA]>=1 And [LOA]<=400,"1 to 400",[LOA]>=100 and [LOA] <=200,"100
to 200")
the Switch always returns: "1 to 400"
I wrote
[LOA]>=80 And [LOA]<=90, it mistyping, cause we have [LOA]<=80 just before
it. then if [LOA]=80, it return the value of [LOA]<=80 first.
Best Regards.

Hi Khoa

Yes that will do, didn't know Switch
it is much easier

You wrote
[LOA]>=80 And [LOA]<=90

has this effect on the result or was it mistyping ?


Thanks for reply

Best regards
Ron



Khoa schreef:

Hi rastolk,
I though your question has been answered, but it hasn't.
Try using Switch function instead of IIf.
For example:
=Switch([LOA]<=50,"50",[LOA]<=75,"75",[LOA]<=80,"80",[LOA]>=80 And
[LOA]<=,"90",[LOA]<=95,"95",[LOA]<=100,"100",[LOA]<=110,"110",aso,aso...)
I haven't check how many arguments could be used with the Switch function -
Check it yourseft.
Hey ! If you use ";" as List Seperator instead of ",", change the "," to ";".
Good luck.

Hi there

think my statements are too complex.


I created a Tblboatmen with 3 fields ID Length and Tarriff
however first record starts with 50mtr and the last record ends 400mtr
The problem is the records don't jump upwards with the same amount
meters
a third party created this tarriff which i have to use.
ID Length Tarriff
1 50 mtr $100
2 75 mtr $120
3 80 mtr $150
4 90 mtr $170
5 95 mtr $175
aso
aso

From a tabel [Ship] the field LOA(LengthOverAll) is filled with the
length of that ship
for example 87 mtr
I use in a report following unbound label named [shiplength]
=IIf([LOA]=<50;"50";IIF([LOA]=>51and [loa]=<75;"75";IIF([LOA]=>80
and[LOA]=<90;"90";aso
aso aso aso if there are too many IIf Access warns "this
is to complex"

The result = 90

To pick the right record in the TabelBoatmen a new unbound label with
=Dlookup("Tarriff";"TblBoatmen";"[length]="&[shiplength]) is created
which gives $ 170

Think above is very complex
Who can clear this for me?

Thanks in advance
Ron