Control Source Expression Builder 1

[calasoren]

Hi, Im wondering in a text box in a report i am attempting to use this expression =IIf([Forms]![Claim Table]![Visible Damages]=True,"1&quot i want to pull the information from a check box in a form so basically its saying if visible damages check box = True or checked put one in the text box or atleast thats what i want it to say. Can anyone see anything wrong with my statment in my report it just says #Name? what does that mean. If anyone could help with this thanks very much

Andrew

 

[calasoren]

If noone can anwser this question can anyone help me with an alternative method of doing this this is only my like 4th time using access. I believe the problem might be that the length of this but im not sure does anyone else know?

 

[67peshawar294]

hi,
first, you need to finish the 2nd part of the IIf statement.
IIf(x=y,z,a) you need the false part as well as the true.
second, it might be better to use -1 for true.

 

[JeremyNYC]

The syntax for iif is iif(test, ResultIfTrue, ResultIfFalse). You've left out the resultIfFalse parameter. You can just put "" if you want nothing to show in those cases.

In general, stuff like this is in help files. But this is a good community, and you'll get lots of help here.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[calasoren]

Ok but i have tried both ways with and without the ending part here is another example that doesnt work just a very simple one.
=IIf([Forms]![Claim Table]![Label70]=[label70],"1","2&quot
it still gives the #name?
yet if i put a label from the current form in there its fine
like IIF([Label14]=[label14], "1", "2&quot
I have also tried the top IIf statment multiple other ways with the forms part both after and before the = sign i tried tons of stuff but up to this point it looks like it doesn't like the length?

 

[JeremyNYC]

It's definitely not about the length.

Is the referenced report open when you try this? The Forms collection only refers to forms that are open.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[calasoren]

im not quite sure what you mean when you say open but if you mean what happens is i run the report no forms or anything else open on the screen just the report and that comes up. I hope this is what you meant

 

[JeremyNYC]

What I'm saying is that unless the form is open when you run the report, this will fail, as it's attempting to reference something on a form, and it can only do that if the form is open.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[calasoren]

Thanks Jeremy that worked for that part its still having an #error on this =IIf([Forms]![Claim Table]![Visible]=True,1,2) but atleast that first error is gone i dont exactly like the fact that the EU is gonna have to open up the report and form im sure my boss will comment on that but for now atleast i will stick with this.

Thanks again for the help

 

[calasoren]

Big question i now understand how you mean it grabs the information from the form but umm i have 1342 records in that form and it doesn't read any record except the currect on pulled up. Is there anything i can do with this statement to do what i need or will i have to learn vba to continue? =IIf([Forms]![Claim Table]![Hidden Damages]=True,"2","1&quot

 

[JeremyNYC]

OK, here's the thing. You don't need the form open. You only need the form open if you want to refer to the value in this way.

A much better way would be to have the HiddenDamages field a part of the recordsource of the report. That way you could use
=IIf([Hidden Damages]=True,"2","1&quot
and it would reflect the right value for every record.

You could even have the above iif statement in the query on which you base the report, and then you'd have the calculation already done for you. That would probably be best.

If you haven't yet played with forms and reports based on queries as opposed to tables, get to it! This will make your objects much more powerful.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[calasoren]

I am attempting to do as you suggested. I am now attempting to put this command or something similar that will work in the criteria for the query im working on

IIf([Hidden Damages]=True,[Reports]![Store Charge New]![Reasons Listing]="1","2&quot

Is there anything like this that will work? I cannot add the fields like hidden damages from the form to the report as the report is going to be printed and needs to take up as little space as possible it will be printed on a usual basis. What i was trying to accomplish with the command above was get the query to feed the report the number 1 or 2 depending on wether it is true of false this is actually gonna be a little different as there are 13 fields like hidden damages that i need in number for so hidden damages will actually put number two visible damages number one. and if they are false they wont take any number at all just a blank?

 

[JeremyNYC]

As I said in the last report, the best solution is to do the calculations in the query upon which the report is based. There is no need to reference either the report or the form. Do the calculations in the query. Get the results you want there. Then base the report on that query.


This part of what you wrote is very confusing to me:
"this is actually gonna be a little different as there are 13 fields like hidden damages that i need in number for so hidden damages will actually put number two visible damages number one. and if they are false they wont take any number at all just a blank?"

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[calasoren]

What i was trying to accomplish with the command above was get the query to feed the report the number 1 or 2 depending on wether it is true of false this is actually gonna be a little different as there are 13 fields like hidden damages that are in the form that must be put in number form in the report. Sorry about making that wierd i couldn't read it either guess i should have over looked what i typed a little more careful. Im trying to get the query to do the equations and such but they are coming from a table initially. Im not good enough at access yet to understand what to do with the query so that it does everything and then get the report to give the results. Hopefully i will get this new access book right away and that will help the ones i have here are horrible. Thanks for all of your help you really have helped alot i got done what i initially intended with your help it just didn't quite work as i had hoped it to.

 

[calasoren]

Its done i think. I went back into the report and i checked the control source and just on a hunch i selected hidden damages. I dont know why i could see hidden damages in the control source but yet i could find it in the expression builder under my current report. But anyway all i did was select it that way then edit it in the expression builder to my liking
=IIf([Hidden Damages]=True,"1","&quot
nice and easy i guess now i will just add the last 12 lines and i should be set to go on this thanks

 

[JeremyNYC]

Try doing something like this in one of the columns in your query's design grid:
NewField: =IIf([Hidden Damages]=True,"2","1&quot

Does that make sense? Then you can do something like that for each of the fields you need translated.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[lchase]

I believe the iif requires 3 arguments( expression ,true value, false value). Could this be your problem.
Len