Formula Problem 3

[janetlyn]

I have the following code in a form:

ElseIf (([A21] <> ([E38] + [E58]))) Then
A21.Visible = True
E38.Visible = True
E58.Visible = True
CumNetPL.Visible = True
Line221.Visible = True
BoxA21E38E58.Visible = True

Obviously this is not all the code, just the relavent ones. If the formula above is not true A21 does not equal (E38+E58) all these fields become visible so the person knows there is an error. If they do equal, the formula goes on to the next ElseIf.

The problem:

A21=(1,455,426.61)
E38=1,000,000.00
E58=(2,455,426.61)

OR

A21=(1,455,426.61)
E38=(1,000,000.00)
E58=(455,426.61)

I have tried both ways and each time, the boxes become visible. I put the exact same formula in an excel spreadsheet and it said they were equal. How come Access does not think these numbers are equal? Any ideas?

Appreciate any help. Janet Lyn

 

[SteveR77]

Janet Lyn,

How are the numbers being formatted?

 

[janetlyn]

They are all currency.

 

[sillysod]

im not sure i understand the problem but...

do you have

ElseIf (([A21] = ([E38] + [E58]))) Then
A21.Visible = false
E38.Visible = false
E58.Visible = false
CumNetPL.Visible = false
Line221.Visible = false
BoxA21E38E58.Visible = false

in the code too?

also have you set the forms to be invisible when the form opens? i think its the on load event

 

[janetlyn]

All my Visible=True are on the Form_Close. My problem is not with the visible false/true, but with the formula itself. I have verified all the visible conditions are correct.

The problem is the numbers ARE equal, but Access is obviously saying they are not, because they are making the conditions True, causing A21,E38...to become visible when they should stay invisible.

I have about 20 of these same problems and the rest are working fine; where a formula determines whether they are invisible or not.

Does this explain a little better? Thanks Silly, JL

 

[cranebill]

I take it these are cells in Excel you are referring to.. if the values of these cells use commas then that may be throwing it off a tad. I could be wrong... I am going to play with it a bit.

Bill

 

[janetlyn]

The A21 is a textbox in the form pulling a number from a report. E38 and E58 are Excel numbers linked to the form. All three have been formatted as currency.

Thanks for all the help. JL

 

[cranebill]

Are all 3 of these textboxes then on this form?

If A21 is a textbox should it not be referenced as Me!A21 or Forms!FormName!A21?

Bill

 

[WildHare]

Janet, I haven't tried this exact code, but you might find it easier to maintain and debug if you alter the logic a bit. I think you can utilize something like this:

A21.Visible = IIF([A21] <> ([E38]+[B38]), &quot;True&quot;, &quot;False&quot

and once you set ONE of these guys, the others can &quot;inherit&quot; the setting:

E38.Visible = A21.visible
B38.Visible = A21.Visible

and so on...

This MIGHT make it easier. There has to be someplace where your logic is failing, because, as you've noted, the numbers DO add up and resolve correctly.....


Jim


Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[MichaelRed]

... but then assuming that the Parens indicate negative numbers, the expr IS True, thus the snippet appears to be correct:

A21 = -1455426.61
E38 = 1000000.00
E58 = -2455426.61

? A21 <> E38 + E58
True

? e38 + e58
-1455426.61

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

p.s. this thread would, perhaps, be more appropiatly placed in the VBA forum, as this one s intended to deal onlty with MS Access issues, not excel spreadsheets.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[WildHare]

Mike, I think this is a FORM question, as evidenced by the poster's original, and third post, where she specifically mentions Access and a Form_Close event.

J

Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[MichaelRed]

WildHare,


Ms. Excel has &quot;forms&quot; (albeit borrowed from Ms. A.). I got the impression (from the RC appearance of the &quot;fields&quot that it was an excel issue. It is quite possible that I was (am) so far off base that I should just &quot;but out&quot;.

The inital posting is so brief (in terms of context clues) that I do not know how many other &quot;bits&quot; of the unexposed snippet might be relevant. It could be as simple as:

If (([A21] = ([E38] + [E58]))) Then
    A21.Visible = True
    E38.Visible = True
    E58.Visible = True
    CumNetPL.Visible True
    Line221.Visible True
    BoxA21E38E58.Visible = false
ElseIf (([A21] = ([E38] + [E58]))) Then
    A21.Visible = false
    E38.Visible = false
    E58.Visible = false
    CumNetPL.Visible = false
    Line221.Visible = false
    BoxA21E38E58.Visible = false
End If

Further confusion arises from the mixed addressing mode (i.e [[a21] vs A21).

Without the &quot;context&quot;, is is quite clear that these are different, but the contest could, perhaps, shead sime insight on this and other murky thoughts.

Sorry for jumping into the deep end of the pool, it was only intended to try to help.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

Jim, I copy/pasted your expression:

A21.Visible = IIF([A21] <> ([E38]+[B38]), &quot;True&quot;, &quot;False&quot

I get the message: Compile Error:Expected:Expression

Crane, I am not sure about the Me! thing, but I have 1 If statement and 10 ElseIf following that work just fine, BEFORE it gets to the one I am having problems with and they are all coded the same. So, if that is a problem, wouldn't it be true for all the If...ElseIf...Then code?

Thanks for the help guys, you are great. Why does it not like the above expression, Jim?

JL

 

[janetlyn]

Michael, I am working on an Access program, with a link to an excel spreadsheet to grab some numbers. When you retyped my code, you typed A = E + E. It is not equals,
rather, it is A <> E + E.

So the boxes, numbers, etc. should appear on the screen when A21 does not match (E38+E58), and otherwise, the code continues to the next ElseIf. Sorry I was so vague. Please help if you can. Thanks, JL

 

[DanJR]

I would have first checked that the values are infact what you think they are, e.g. output the 3 values to the immediate window (debug windows):
[tt]
debug.print &quot;[A21]=&quot; & [A21]
debug.print &quot;[E38]=&quot; & [E38]
debug.print &quot;[E58]=&quot; & [E58]

msgbox &quot;Debug - Evaluate Expression: &quot; & [A21]=([E38]+E58])
[/tt]
I also think you should re-read Micheal's last post.....does vba actually get the chance to evaluate the elseif expression? Have you put a breakpoint at the start of the If statement, then step through the if statement to see what actually gets executed?

Cheers,
Dan

 

[janetlyn]

Pretend you are a surgeon talking to an ambulance driver, or better yet, just the mechanic of an ambulance. I have never done the &quot;debug&quot;, &quot;step&quot;, &quot;output&quot;, etc. However, I know it is evaluating the formula, because everything is non-visible until it does evaluate the formula. If the numbers do not match, the next lines of code, make those textboxes associated with the wrong numbers visible. The code then stops and will not go on until the numbers do match. Could you explain it from an idiot's point of view how to put that formula in the debugger and have it tell me what the answer is?

I went back to Michael's comments:

&quot;Further confusion arises from the mixed addressing mode (i.e [[a21] vs A21).&quot;

The ones in brackets are all part of an If or ElseIf statement and I thought they had to be in brackets. Everything I do is by looking at examples and the example for If had brackets, the example for Visible did not. Like I said previously, the other If.ElseIf statements performed before this one are working.

What other clues can I give that would help explain the situation? Thanks so much, JL

 

[WildHare]

Janet, my code should have worked, at least syntactically, here's what I tested with in a form of mine:

With Me
.Label11.Visible = IIf([Text0] <> ([Text2] + [Text4]), &quot;TRUE&quot;, &quot;FALSE&quot
End With

My &quot;label1&quot; guy just reads &quot;they are NOT EQUAL&quot;, and becomes visible if I type 10 in text0, 5 in Text2 and 4.9999999 in Text4..

If your db isn't really large, complex or full of state secrets, feel free to zip it and send it to me and I'll take a quick look. I can accept A2K or a97 versions. My email address can be found on th website mentioned in my sig lines...

[red]Mike[/red] - no problem...I know what you mean.



Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[janetlyn]

Here is my code:

A21.Visible = IIf(([A21] <> ([E38] + [B38])), &quot;True&quot;, &quot;False&quot

Now it says, &quot;Run time error 2465: Project Database can't find the field '|' referred to in your expression.&quot;

Neither can I. I did a search of the whole project using &quot;Find&quot;, and it could not find the '|' symbol, so now I do not know what it is looking for. Do you have any ideas?

I will be working on this all weekend, so if I hear from you great, otherwise, maybe we can &quot;talk&quot; Monday.

If you don't know what the symbol is, or if I'm just to loopy to figure out the problem, I will zip it over to you. Thanks again, JL

 

[DanJR]

1. When you are viewing the window that has the code in it, you can press Ctrl+G or goto the menu bar and select View > Immediate Window (Access 2000 & above) - its probably called 'debug window' in Access 97. This will show you the immediate window where the results of any 'denug.print' code is outputted to.

2. copy and paste the code following directly above the If..ElseIF.. statement:
[tt]
debug.print &quot;[A21]=&quot; & [A21]
debug.print &quot;[E38]=&quot; & [E38]
debug.print &quot;[E58]=&quot; & [E58]
debug.print &quot;Is This Statement True ([A21]=[E38]+[E58]): &quot; & [A21]=[E38]+[E58]
msgbox &quot;Debug - Evaluate Expression (Equals): &quot; & [A21]=([E38]+E58])
[/tt]
3. Test the application as you have been doing, but this time, after every time you run the application you will see some information added to the immediate window e.g. it will look something like:
[A21]=-1445426.61
[A38]=1000000
[A58]=-2455426.61
Is This Statement True ([A21]=[E38]+[E58]): True

Copy and paste this info to the tek-tips forum for all to see.

4. You can &quot;step through&quot; the code by doing the following: Click on the first line of the If-statement, then goto the menubar and select Debug > Toggle Breakpoint (or press F9). That line of will turn brown. Now, run you application and the code stop and this 'breakpoint' and highlight the line of code yellow. This yellow line shows you what the next line of code that is about to be executed. Press F8 to execute that line and goto the next line of code. You can now see what order the code logically runs in and which bits of code do and do not get executed.

Cheers,
Dan