IIf problem

[Sven]

Hi,

I want to use an IIf in a textbox in my report. I have the following code in the control source:

=IIf([filmlijst.status]>2,"d","e")

the error given is: invalid syntax, you may have entered an operand without an operator

As I searched on this item, I don't really see the problem? Everywhere, they use the same syntax as I?

thx

Sven

 

[Sven]

no it just stays the same, I really don't understand it

 

[Sven]

sadly enough it wasn't a column name, so I still have the problem.

grtz

 

[Sven]

yeah it's not really about the sources i guess, is it possible that access doesnt recognise the 'iif'?
It's an english version, so normally there wouldn't be a problem. But in the access-help pages, I can't find anything on it either.

 

[Fear Naught]

IIF is a valid function in Access and can be used in reports. Your problem is certainly to do with the control you are doing the comparison on.

What is "[filmlijst.status]"? As Pat states above this has to be part of the RecordSource behind the report.

Can you post the SQL of the querY that forms the RecordSource. This would help us solve your problem.

 

[Wolff]

just to put another slant on it, is filmlijst the table, and status the field? If so, try [filmlijst].[status]

alternatively, if field is filmlijst.status, try putting in quotation marks e.g. ["filmlijst.status"]

Might help, or then again, may have already been tried.

Good luck!

 

[mihabaki]

Hi,
=IIf([filmlijst.status]>2,"d","e")

Sven

you should make sure that you have put just the field [filmlijst.status] somewhere on your report first, only then can you reference it in a formula...

 

[Sven]

SELECT titel, duur, grootte, type, locatie, [resolutie-breedte], [resolutie-hoogte], bitrate, filmlijst.status, status.status 
FROM filmlijst 
INNER JOIN status 
ON filmlijst.status=status.id 
WHERE filmlijst.status<>'2' 
ORDER BY filmlijst.titel;

this is the recordsource I have. filmlijst.status was already in there, so I don't think that's the problem.

The other solutions also dont work. Even if I change the filmlijst.status to an other field, like 'type' or 'location'..

It just keeps on saying I have entered an operand without an operator?



* just a little problim asside this: how to use a query inside a control source? I have an other field where I want to add this one:

SELECT Sum(filmlijst.duur) AS SumOfduur FROM filmlijst GROUP BY filmlijst.type, filmlijst.locatie HAVING type=[type]  AND locatie=[locatie]

This one is to replace the '=sum(duur)' I had before, for a series of records. 'duur' is a time-field, but if I used the '=sum(duur)', I came to the following result:
A
1
2
sum(duur) = A1 + A2

B
1
2
3
sum(duur) = A1 + A2 + B1 + B2 + B3
(this should only be B1 + B2 + B3)

 

[mihabaki]

this is the recordsource I have. filmlijst.status was already in there, so I don't think that's the problem.

The other solutions also dont work. Even if I change the filmlijst.status to an other field, like 'type' or 'location'..

It just keeps on saying I have entered an operand without an operator?

I know that you have added the field to your report source, but did you add this field to the report (in design view) ???

p.s. it would help if you could post a picture of your report in design view; or post the database...

 

[Rich]

The field does not have to be on the report in order to refer to it.
It only has to be in the underlying record source of the report

 

[Rich]

You've referred to the number two as a number in your Iif but as text in your SQL, in which case I doubt if the Iif will work as you expect it to
Change the data type to a number and change your SQL to suit

 

[Fear Naught]

Another problem could be the datatype of filmlijst.status. You are testing it (in your IIF expression) for an integer. What is the datatype set to in the table?

A long shot I know but it is just this sort of mix up that makes Access throw it's teddies out it's cot!

 

[Rich]

Could have sworn I just said that

 

[Fear Naught]

It looks like "Rich" and I came up with the same idea at the same time.

"Great minds think alike" Rich - or is it "Fools Seldom differ"

 

[Rich]

He could try this
Iif(Val([filmlijst.status]>2),"d","e")

 

[Sven]

Hm, I just can't find it.

I tried it now with 'bitrate', is an integer but doesn't work either.
Also tried with other text boxes standing in the form, no luck..

I have uploaded the db:
http://users.telenet.be/naait/problem.mdb

Hope it's just something stupid..

 

[john471]

First thing - consider doing a compact & zipping your DB before uploading - doing this dropped the final size from 1.69MB to 16KB!

Second thing - you have enclosed the formula in quotation marks ? Why ?
When I change
="IIF(bitrate>2,'do this','do that')"
to
=IIF(bitrate>2,'do this','do that')

I get "do this"es and "do that"s on my report. Actually they are all "do this"es , but that is because the bitrates are all >2 (by a long shot) they range from 699 to 2044. If I further change it to
=IIF(bitrate>1000,'do this','do that'), I observer the (presumed) desired result.

For your original "filmlijst.status" problem,
adding a new textbox named "Text56" in my case, you can name it anything you want as long as it is NOT : a reserved word; the same as a field name. I would typicaly go with txtFilmlijstStatus. Set its Control source to
=Iif([filmlijst.status]>2,"d","e")

And I get what I presume is your desired result. (Using WIN2K/Access XP)

Other things:
1) you have a textbox named "filmlijst_status" for which the Control Source is duur. That doesn't seem logical.
2) Several of your objects (text boxes, labels etc) are named the same as columns in the record source - this is not a good idea for reasons described in previous posts in this thread.

HTH

Regards

John

 

[Sven]

ok, thats VERY strange.

the reason I added the quotes, is because of the error I get otherwise?

I also had
=IIF(bitrate>2,'do this','do that')
as formula, but access would give me a prompt errorbox, saying 'you may have entered an operand without an operator'

Thats why I added the quotes.

Would you be so kind to upload your version of the database? I really don't know why it won't work

And thanks for the other advices Indeed, the code is a bit messy, but just because I'm still developping it. The naming of the textboxes is something I haven't considered doing yet.

thanks!

 

[mihabaki]

Sven, could you try putting the field you are referencing to the report first! and then reference it as you wish...

e.g.

1. put a field [bitrate] on the report (you can set it's visible option to false)
2. and only the put the field: =IIF(bitrate>2,'do this','do that') to the report

I think this should work

 

[john471]

Ensure you don't have any missing references.
Try a compact & repair on the DB.
Then open a code module & compile & save all modules.

Did any of that help ?