Having problem with switch expressions.

D

DaveC271

I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Click to expand...
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going wrong.
 
K

Ken Snell [MVP]

Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Click to expand...
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Click to expand...
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going wrong.
Click to expand...
 
S

Sylvain Lafontaine

In such a case, using a user defined VBA function might also be of some
help.

S. L.

Ken Snell said:
Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Click to expand...
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Click to expand...
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going wrong.
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Perhaps... I have found that user-defined functions in queries can make the
query run much slower than when using the built-in VBA functions. So it may
be worth a try.

--

Ken Snell
<MS ACCESS MVP>

Sylvain Lafontaine said:
In such a case, using a user defined VBA function might also be of some
help.

S. L.

Ken Snell said:
Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Click to expand...
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150, [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,[Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Click to expand...
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going wrong.
Click to expand...
Click to expand...
Click to expand...
 
D

DaveC271

I now get the following error message

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)


Ken Snell said:
Perhaps... I have found that user-defined functions in queries can make the
query run much slower than when using the built-in VBA functions. So it may
be worth a try.

--

Ken Snell
<MS ACCESS MVP>

Sylvain Lafontaine said:
In such a case, using a user defined VBA function might also be of some
help.

S. L.

Ken Snell said:
Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150, [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,[Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going
wrong.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Not sure if you have a copy/paste error, but let's try a simpler approach:

LandRegistryFeeEXPR: IIf([Property Details]![Purchase Price] <=
50000,60,IIf([Property Details]![Purchase Price] <=80000,80, IIf([Property
Details]![Purchase Price] <=100000,100, IIf([Property Details]![Purchase
Price] <=200000,150, IIf([Property Details]![Purchase Price]
<=500000,250,IIf([Property Details]![Purchase Price]<=1000000,450, 750))))))


--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I now get the following error message

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)


Ken Snell said:
Perhaps... I have found that user-defined functions in queries can make the
query run much slower than when using the built-in VBA functions. So it may
be worth a try.

--

Ken Snell
<MS ACCESS MVP>

Sylvain Lafontaine said:
In such a case, using a user defined VBA function might also be of some
help.

S. L.

Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150, [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,[Property Details]![Purchase Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,( [Property
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong
Click to expand...
number
of
arguments."

I am trying to get the following outcome and use it in other expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going
wrong.
Click to expand...
Click to expand...
Click to expand...
 
D

DaveC271

This has worked thanks. Would I be able to use this if I wanted to output
text rather than numbers

Ken Snell said:
Not sure if you have a copy/paste error, but let's try a simpler approach:

LandRegistryFeeEXPR: IIf([Property Details]![Purchase Price] <=
50000,60,IIf([Property Details]![Purchase Price] <=80000,80, IIf([Property
Details]![Purchase Price] <=100000,100, IIf([Property Details]![Purchase
Price] <=200000,150, IIf([Property Details]![Purchase Price]
<=500000,250,IIf([Property Details]![Purchase Price]<=1000000,450, 750))))))


--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I now get the following error message

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)


Ken Snell said:
Perhaps... I have found that user-defined functions in queries can make the
query run much slower than when using the built-in VBA functions. So it may
be worth a try.

--

Ken Snell
<MS ACCESS MVP>

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message In such a case, using a user defined VBA function might also be of some
help.

S. L.

Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase
Price]
<=200000,150, [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,[Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase
Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And
[Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,(
[Property
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number
of
arguments."

I am trying to get the following outcome and use it in other
expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going
wrong.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Yes.

--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
This has worked thanks. Would I be able to use this if I wanted to output
text rather than numbers

Ken Snell said:
Not sure if you have a copy/paste error, but let's try a simpler approach:

LandRegistryFeeEXPR: IIf([Property Details]![Purchase Price] <=
50000,60,IIf([Property Details]![Purchase Price] <=80000,80, IIf([Property
Details]![Purchase Price] <=100000,100, IIf([Property Details]![Purchase
Price] <=200000,150, IIf([Property Details]![Purchase Price]
<=500000,250,IIf([Property Details]![Purchase Price]<=1000000,450, 750))))))


--

Ken Snell
<MS ACCESS MVP>

DaveC271 said:
I now get the following error message

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)


:

Perhaps... I have found that user-defined functions in queries can
Click to expand...
make
the
query run much slower than when using the built-in VBA functions. So
Click to expand...
it
may
be worth a try.

--

Ken Snell
<MS ACCESS MVP>

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message In such a case, using a user defined VBA function might also be of some
help.

S. L.

Try this:

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,[Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80, [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100, [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase
Price]
<=200000,150, [Property Details]![Purchase Price] >200000 And [Property
Details]![Purchase Price] <=500000,250,[Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450, [Property
Details]![Purchase Price] >1000000,750)

--

Ken Snell
<MS ACCESS MVP>

I am having problems trying to run the following switch function.

LandRegistryFeeEXPR: Switch( [Property Details]![Purchase Price] <=
50000,60,( [Property Details]![Purchase Price] >50000 And [Property
Details]![Purchase Price] <=80000,80,( [Property Details]![Purchase
Price]
80000 And [Property Details]![Purchase Price] <=100000,100,( [Property
Details]![Purchase Price] >100000 And [Property Details]![Purchase
Price]
<=200000,150,( [Property Details]![Purchase Price] >200000 And
[Property
Details]![Purchase Price] <=500000,250,( [Property Details]![Purchase
Price]
500000 And [Property Details]![Purchase Price] <=1000000,450,(
[Property
Details]![Purchase Price] >1000000,750))))))

I get the error message

"The expression you entered is missing a closing parethesis, bracket or
verticle bar."

When I add a bracket on the end I get the error message

"The expression you entered has a function containing the wrong number
of
arguments."

I am trying to get the following outcome and use it in other
expressions.

LAND REGISTRY RULES TABLE
PropertyPrice Amount Payable
0-50k 40
50k-80k 60
80k-100k 100
100k-200k 150
200k-500k 250
500k-1000k 450
1000k+ 750

Please can somebody take a look at this and tell me where I am going
wrong.
Click to expand...
Click to expand...
Click to expand...
 
Top