Solved My Switch() function won't work in a report. Can anyone help me?

[danititiriga]

Hello.
I am trying to make a report (from a movie list) in which, according to the length (in minutes) of the movie, Access displays a text message in my report.

I tried using an iif function but to no avail. I then decided to turn to a switch function, but I kept on getting the following error message: "The expression you've entered contains invalid syntax. You may have entered an operand without an operator." And when I clicked enter and went into report view anyway, in that field was displayed the following text: "#Type!".

This is the function that I have entered in the ControlSource field: "= «Expr» Switch([durata]=120;"Durata e de doua ore";[durata]=90;"Obisnuit'";[durata]>120;"Doua serii";[durata]<120;"Doua serii") ". The [durata] field is the length of the movie in minutes. The first value displays a message that says that the movie is 2 hours long. The next one says that it's of normal duration and the last two say that it's a series of movies.
Can anyone tell me what is wrong with the syntax in this function, please?
I can't find any solutions anywhere.

 

[Deleted member 147267]

Here I would use commas ( , ) as separators and I see you are using semi-colon ( ; ). I don't know if that would be a problem or if Access will just use ; if it is specified as the separator character in you regional settings. If you look at M$ help page, it shows comma but does not explain if regional settings for separators have any effect. Try , instead of ;

Also, in your example, you seem to have one trailing " . Is that a typo, or do you have an odd number of quotes?
Doua serii") "
You also have a stray single quote? Obisnuit'";

I would fix the quotes first.

 

[Isaac]

Take «Expr» out of the expression.

 

[Deleted member 147267]

Take «Expr» out of the expression.

Hmm, I just thought that was a place holder for something OP didn't want us to see, but after looking closer, I see that there is no unmatched ending quote, but there seems to be an unmatched single quote. Perhaps there a 2 issues, or I need to put my glasses on.
I know - no coffee yet.

 

[danititiriga]

I did all the things you suggested, but it still won't work. It doesn't give me the syntax-related error anymore(that was definitely because of the stray '), but when I run the report, it still returns the message "#Type!". The last column is the one I used that function for.

This is the way I entered it:

Also, I think the semicolon is a regional thing, because I tried using a comma and it gave me the same syntax error.
Thank you for helping me!

 

[Isaac]

Can you post your current Expression for us?

 

[danititiriga]

=Switch([durata]=120;"Durata e de doua ore";[durata]=90;"Obisnuit";[durata]>120;"Doua serii";[durata]<120;"Doua serii")
There it is.

 

[Isaac]

Check this out a bit before retrying. https://www.techonthenet.com/access/functions/advanced/switch.php

According to that, all of those semicolons are supposed to be commas.

 

[Minty]

Try renaming your control to txtDurata rather than it being the same as the field you are referring to.

 

[Deleted member 147267]

I suspect you cannot assign a property using this function because there is no <exp> placeholder on the left.
There is a yellow warning triangle next to your expression. What is it reporting?

 

[Deleted member 147267]

I suspect you cannot assign a property using this function because there is no <exp> placeholder on the left.

Well, that is not the case. In a test report, it worked when the control and field have the same name. Then it worked when I changed the control name and left the reference to the name of the field.

 

[danititiriga]

Well, that is not the case. In a test report, it worked when the control and field have the same name. Then it worked when I changed the control name and left the reference to the name of the field.

I changed the name of the control, as suggested by @Minty and it turned out that that was the error being reported by the yellow triangle.
But then, probably because it couldn't find the [durata] field anywhere when I ran the report, it asked me to insert a value for that field. I then tried to declare the field like this: =Switch([FILM]![durata]=120;"Durata e de doua ore";[FILM]![durata]=90;"Obisnuit";[FILM]![durata]>120;"Doua serii";[FILM]![durata]<120;"Doua serii")
But it's still asking me to insert a value.

 

[Isaac]

I have almost never used Switch() so cannot be of much more help, but I just wasn't sure about those semicolons..

 

[danititiriga]

I have almost never used Switch() so cannot be of much more help, but I just wasn't sure about those semicolons..

Thanks anyway! But I did try switching the semicolons for commas and it gave me that syntax error. So I reckon it's definitely a regional thing.

 

[Deleted member 147267]

it turned out that that was the error being reported by the yellow triangle.

What was the message - something like 'ambiguous name detected' or something similar but less cryptic?
FYI on your current situation - a report must have a control bound to a field in order to refer to that field. On a form you can refer to a field in the recordsource even if there is no control on the form for that field, but you cannot do that on a report. So I'm guessing that's your problem because you don't say what field the prompt is for. At least I think that's what you mean by "it's asking me to insert a value".

 

[MajP]

Switch is a waste of time. Not flexible and inefficient. Data goes in table and not in code.
tblDuration
tblDuration

ID	minDuration	MaxDuration	Message
1​	0​	90​	Less than 90
2​	90​	91​	90 minutes
3​	91​	120​	Greater than 90 and less than 120
4​	120​	121​	120 minutes
5​	121​	1000​	Greate than 120 minutes

tblMovie

ID	MovieName	Duration
1​	Movie 1	80​
2​	Movie 2	90​
3​	Movie 3	92​
4​	Movie 4	119​
5​	Movie 5	120​
6​	Movie 6	125​

SELECT tblMovie.MovieName, tblMovie.Duration, tblDuration.Message
FROM tblMovie, tblDuration
WHERE (((tblMovie.Duration)>=[tblDuration].[MinDuration] And (tblMovie.Duration)<[tblDuration].[MaxDuration]));

Query1

MovieName	Duration	Message
Movie 1	80​	Less than 90
Movie 2	90​	90 minutes
Movie 3	92​	Greater than 90 and less than 120
Movie 4	119​	Greater than 90 and less than 120
Movie 5	120​	120 minutes
Movie 6	125​	Greate than 120 minutes

 

[danititiriga]

What was the message - something like 'ambiguous name detected' or something similar but less cryptic?

The error message was 'this control has a reference to itself'.

FYI on your current situation - a report must have a control bound to a field in order to refer to that field. On a form you can refer to a field in the recordsource even if there is no control on the form for that field, but you cannot do that on a report. So I'm guessing that's your problem because you don't say what field the prompt is for. At least I think that's what you mean by "it's asking me to insert a value".

Yes, that's the problem I'm encountering.

 

[Isaac]

The error message was 'this control has a reference to itself'.

That kind of answers the whole thread. You cannot refer to the control which hosts the expression, within the very same expression!

 

[danititiriga]

Switch is a waste of time. Not flexible and inefficient. Data goes in table and not in code.
tblDuration
tblDuration

ID	minDuration	MaxDuration	Message
1​	0​	90​	Less than 90
2​	90​	91​	90 minutes
3​	91​	120​	Greater than 90 and less than 120
4​	120​	121​	120 minutes
5​	121​	1000​	Greate than 120 minutes

tblMovie

ID	MovieName	Duration
1​	Movie 1	80​
2​	Movie 2	90​
3​	Movie 3	92​
4​	Movie 4	119​
5​	Movie 5	120​
6​	Movie 6	125​

SELECT tblMovie.MovieName, tblMovie.Duration, tblDuration.Message
FROM tblMovie, tblDuration
WHERE (((tblMovie.Duration)>=[tblDuration].[MinDuration] And (tblMovie.Duration)<[tblDuration].[MaxDuration]));

Query1

MovieName	Duration	Message
Movie 1	80​	Less than 90
Movie 2	90​	90 minutes
Movie 3	92​	Greater than 90 and less than 120
Movie 4	119​	Greater than 90 and less than 120
Movie 5	120​	120 minutes
Movie 6	125​	Greate than 120 minutes

I'll try doing it this way too. Thank you very much!!

 

[danititiriga]

That kind of answers the whole thread. You cannot refer to the control which hosts the expression, within the very same expression!

Yeah. But when I fixed that problem, others occurred. Hopefully, I can figure this out. Thanks for the help!