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

 

[janetlyn]

Gosh Michael, not a monkey, more like a baboon. I'm assuming what code you wrote three boxes up is for me to put somewhere in my code to find the | the code is looking for? If so, I have absolutely no idea where to put it to check, and/or if I need to change anything to make it match my names (field, table, form, etc.) to conform to what I need.

Also, as mentioned earlier, I copied your complete code and replace mine, corrected a few things, and now it opens the form, and evaluates the first IF statement. They became visible because it is not picking up my Var1, Var2, now. I have no idea in your code where that is. However, the code debugger thing highlighted in yellow the following:

Idx = (The first one that = 1)

Compile Error: Variable not defined

This code is in the midst of the following:

Idx = 1
While Idx <= 33
Me.Controls(&quot;A&quot; & Trim(Idx).Visible) = False
Idx = Idx + 1
Wend

Any clues to the problem? Thanks both Michael and Cajun. Janet Lyn

 

[MichaelRed]

I did not note / notice any 'block' of declarations (&quot;Dim SomeVar As ThisType&quot, only individual declarations immediatly above their first use. While this is not my usual practice, I thought I had followed your convention and (again thinking <> doing) placed the line:

[tab]Dim Idx as Integer

Immediatly above the line you cite in the above post.

While on other matters (yesterday) the concept of what you are attempting to accomplish finally wriggled past my mental defense mechanisim(s) and I realized that both you original code and my change are probably NOT accomplishing what you 'said':

&quot;if a group of the numbers are not the same, such as A1, E50, or Q3, then Those three numbers appear with a note of where they were garnered so the person can go find their input error and then rerun the queries again, checking the matches. So all numbers are the same, else it displays said numbers&quot;

This implies that EACH block should be seperatly evaluated and ALL groups of mis-mathed values should be displayed/checked /evaluated. Both your original structure (large number of ElseIf 'blocks' and my change to individual If blocks present one amd only one such mis-mstch. This, in turn, implies (requires) the user to re-run the procedure repeatedly until there are no more controls displayed. From other parts of your discussion and the overall structure of the procedures, this further implies that the form must be closed and re-opened until NO controls pop-up. Which is a bad thing! In my structure, you can make all error conditions apear at the same time by removong the last line in each of the individual If Blocke (the one which says &quot;GoTo CondFnd&quot; (in retrieving this I also noticed that I left off the GoTo in some of the blocks, so in every case {Block} you need to either rempove the line or fix it}.

With the number of possible groups of controls to be revealed it further seems likely that the approach / soloution uses a fairly small form and the various groups would overlap, so additional effort would be necessary to change the placement of the groups of controls to avoid the overlap.

More woes occur to me in that the process seems like the logic is somewhat reversed. I would NOT display a &quot;finished&quot; button until all the relevant criteria were met. This would, in part, provide an alternative (&quot;Cancel&quot;?) button, to allow the user to exit gracefully which would ALWAYS be displayed, and enable the finished button ONLY when all of the criteria statements were satisfied. Additionally, it should be possible to have the criteria refreshed after any corrections are made to the 'source documents'.

Opening numerous reports to retrieve ONE value from each seems like it is more 'thrashing' (and time consuming) than should be necessary. Generally, reports include ONLY values directly from or based on values in the reports. The more common approach would be to get the value (or their elements) from the 'recordsource' {query}. not open the actual report.


While I obviously do not know the overall environment of this app, the above and a few other issues give me some concerns about the application and wheather it is doing what the business needs. I see numerous potential faults which could propigate incorrect information throughout the business unit, which -in turn- could provide generate bad decisions .

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

Michael, see you are a genius; no matter what anyone says. You have hit the nail on the head. Almost everything you said is true. The only part that is not is the blocks overlapping each other. Sorry, there was another thing (not sure if it is untrue or just an opinion thing) but, when the ElseIf statement is true then it displays those numbers and the box with the description. You stated you it would be better to see them all. For this application, I believe I would like to make them stop and fix the one mistake and then &quot;refresh&quot; (you are absolutely right in that it is having to go through ALL the reports again although with my small program it is not taking much time). The reason is if they fix one mixtake, it might eliminate two or three other formula falses and they will never know they had four groups wrong. For example, One comparison is I have 6 employees. Each work so many hours a month/year. Those hours are input into three separate spreadsheets in different formats. Those hours are also inputted into Access. The comparison would be to 1) add up all the hours for each of the employees in each separate spreadhsheet and access and make sure all four numbers match FOR THE MONTH; 2) same, except for the year. So if one person's hours are off, say I input 50 instead of 40, with your scenario it would show two groups that are wrong and now visible. In my example, it would only show one. That one would be fixed and they would never know about the other one.

While I am typing this my immediate thinking is, well then why even having the second check. However, because there are so many variables, over the four years I have been developing all these spreadsheets, database, etc. I have set up one Grouping whenever a situation arose that required it because the checks I had did not catch it. That probably does not make a heck of a lot of sense, but hopefully you get the jist of what I am trying to explain.

As a matter of fact, last night while I was trying to fix some of the code, I had a thought (kinda scary, I know): As you stated, instead of having to open all these reports and get numbers, why not have a form that goes and picks up all the data through queries and then my form can compare the numbers from that new form. The reason I was thinking of going that route is because my parameters are not working correctly, and so one query of all reports, a number is showing up 13,000. The next re-query it is showing up as 0. And it keeps going back and forth and I cannot figure out why. However, I believe that is what I was going to do in the first place, and someone else from Tek-tips, had me set it up with Public Variables gathering the numbers from reports. So, I am going by what whoever helps me says is the best way to do it. I don't know the best way, I just want it to work. However, there are many ways for problems down the road the way it is setup so you are right that I need to change things.

By the way, I figured out last night why it was looking for &quot;|&quot;. From your statement above I thought you were telling me it is not one specific thing but lots of things messing up, so I start going through the code line by line. I found some brackets missing and some parens missing or too many. That fixed that problem.

I have everything working now except trying to figure out why it cannot give me the same number on that one report each time it is ran. So, that is where I stand at this time. After you have pondered all this, I would love any suggestions that would streamline the whole process and make it more efficient and error proof. If it would help, I would be more than happy to send you a copy of the DB and you could get a few laughs over all my mistakes.

I have to do a bunch of government reports now, but I hope to get back to &quot;programming for babies&quot; by the weekend. I'm open for all suggestions and thanks for your continued help. Janet Lyn

 

[MichaelRed]

Well, Good! At least your primary issue has been resolved.

While I would not object to reviewing your app, I do not know that such a review would be all that useful. Your app apparently uses a multitude of data sources, so without all of them them it would not even execute. More importantly, an application of this nature operates in a BUSINESS environmemnt as much as a programming environment. Without some knowledge of the business rules and goals, a review can only make suggestions re the structure of the programming. An example would be the difference in revealing of the groups of controls based on the various comparisions. You prefer the current 'one group at a time' process, while I would suggest the all at once approach. This is not a necessarily right / wrong selection but one which is (or should be?) based on the business environment. I would probably not even actually 'show' any of the control groups in the manner you are doing it, but provide a tab control with a tab sheet for each group and a seperate tab sheet for the various comparisions with an unouund group of option buttons representing the criteria. The option buttons would simply indicate the tab sheets which included the areas (control groups/ tab sheets) where issues were encountered. Further, it would SEEN like that the app coul (cooulda, woulda, shoulda?) not JUST show the problems to the Users, but provide the capability to actually correct the individual errors from within the application. Since Ms. A. CAN operate as an &quot;Office Automation&quot; client or server, and it MUST be known &quot;where&quot; (which office &quot;document(s)&quot; need to be referenced, the control groups should be capable of instantiating the necessary application and the specific &quot;document&quot; which the user needs to change / correct.

Even more fundamentally, the very nature of data processing implies that V&V should occur at the earliest point possible in the process. In program design, there is a relatively common belief that the cost to correct a design flaw rises exponentially which the time it remains in the system design. At one place where I was employed for quite a while, we had an unofficial motto: &quot;There is never time to do it right, but there is always time to do it over&quot;. I believe this to be more true of data processing than it was for the products which we were developing.

An approach which I would recommend is that EACH (AND EVERY) datum entry should have as thorough a verification and validation routine as possible. It is one of, if not THE reason I dislike spreadsheets in general. I have to quit -at least for now. I will consider this more at some later time.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

Wow, thanks Michael. I will explore the idea of tabs. Had not thought of that, probably because the one time I tried to use them I failed miserably, so have never taken the time to learn to use them correctly. Unfortunately, the computer will not be able to correct input mistakes automatically, because the error could be in a multitude of places. And if three numbers in a group don't match, until I investigate and the mistake I will not know which number is the correct number the others should match to. Actually the database only links to one Excel sheet. The numbers may range over 30 spreadsheets, but they change so much in layout each month, I put all the numbers to be verified in one sheet (which also must be hand-input). Fortunately, I am the only one who will ever use this program until I die, get run over, or fired.

One question if you get back to this. I know what a tab control screen look like. If you had one checking group to a tab, would that require the person to actually point to each tab, make sure the numbers match, go to the tab, make sure numbers match, etc.? Or just have those that are wrong have their tab showing? Is that possible?

Just food for thought. My db is only 17k and the Excel spreadsheet is tiny. If I get desparate I may ask you to take a look. Let me know about the tabs please. Your wonderful, have a great day. JL

 

[MichaelRed]

Ther are numerous approaches to accessing the 'tab sheets'. I would probably just make the ones w/o visible controls disabled. You can then either have hte user select the tab, or use the option buttons to select them or even programatically select the first one which is enabled. In the &quot;Automation&quot; sense, my reference was to having the logic simply open up the necessary application(s) and Documents for the review and correction process from your application.

The note re V&V was 'aimed' toward the concept of placing such in the Source data input / collection processes. In general terms, hmuan input is the most prone to the generation of errors. While there are many errors which cannot be distinguished, there are also many which can. A simplistic example might be entering time sheet information in 'decimal' format, one might (inadvertantly, of course) intend to enter 8.5 hours, and miss the decimal point. Since the (normal?) work day cannot exceed 24 hours, it is quite easy to detect the discrepancy at the time of its' entry, thus keeping it from being an input to your application. Again, w/o knowing the process, it is not possible to even begin to suggest realistic enhancements such as this, so it is only an example.

Re re-entering data, this is, in my opinion, the more or less worst possible approach. It represents at least a duplication of effort, and depending on other factors a MORE THAN DOUBLE cost factor. In any real / reasonable situation you shoud make standardization of your data sources a priority. I, personally, have written additional db apps for different departments and refused to (thereafter) accept data in any format other than that supplied by those apps specifically to eliminate the redundancy in data entry.

Duplication of this effort usually introduces additional errors, as (at least in my experience) there have never been any perfect data entry operators in the history of 'keyboarding'.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

Again, fantastic. Had not thought about things like no more than 24 hours in a day. However, I am such a newbie at all this and I have to do it so sporadically (so I can complete my other work) that I cannot remember how to do a bunch of coding. I believe if I can just get it to work now (the reconciliation form) than as time goes by, I can make additional improvements like checking data entry.

I wholeheartely agree with the input error problem. This company is only 5 years old. It is an engineering firm and there are only 6 of us. When I got here they only had Quickbooks set up and a simple spreadsheet that did their invoicing. Everything set up has evolved over those five years from problems that has risen when doiong invoicing. I work for three perfectionist who HATE mistakes. So everything I have set up (mainly the reconciliation which double checks all numbers) is to avoid having mistakes on any documents (financials) given to the employer. When I do financials I KNOW the numbers are correct because somewhere somehow, they have all been double-input. We are not really talking about a lot of input anyway since we are so small. I believe double-inputting would be much preferred than a snide remark when a mistake is found.

If I ever get the reconcilation form working, I believe I will just copy the database and work on the copy making tab sheets. I can see several places that would be helpful and would cut down considerably the number of forms I have.

I realize you have spent a ton of time with me and I much appreciate it. If you have any thoughts down the road that might help, please let me know. Have a wonderful day. Janet Lyn

 

[WildHare]

[red]Mike[/red] wrote:
In general terms,[red]hmuan[/red] input is the most prone to the generation of errors

Sorry, [Red]Mike[/red], couldn't resist that one. I'm sure you wish, as I OFTEN do, that there was a way to edit a post once it's been submitted...

I have nothing useful to add, except that I agree with you re: data validation & verification. I have several apps where a great deal of the Access data starts off in Excel, and I've had to stand on my head and spit nickels to clean the data up. I even went so far as to add data validation checks to the Excel spreadsheets whenever possible, and that helped a bit.

See Ya!

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

 

[janetlyn]

Jim, how did you add validations to Excel spreadsheets? JL

 

[WildHare]

For any cell with DATA in it, click DATA, Validation, and then pick from the available options. For example, you can restrict data to between an upper and lower bound. There are other validation criteria you can apply as well. It's not quite as extensive as Access or any database oriented program, but it helps.

I've also done checking via a formula NEXT TO a pieces of data, that becomes VISIBLE if there is a detectable (keystroke) error - such as like this:

=IF(A34 <> B35, &quot;*****BAD DATA, CHECK INPUT****&quot;, &quot;&quot

This provides VISUAL CLUE that there may have been a keystroke error - but can't FORcE the user to correct it. For that, I've found a whip, a large caliber hand gun, and a good strong set of jumper cables is the only surefire way to convince....

Of course, if you have this kind of thing, I'd would have a formula that REFERS to the original data in the first place, rather than have the user type the SAME piece of data in two different locations in a sheet. If you think about it, and plan your sheets well enough, there is an awful lot you can do to make sure the input data is as clean as humanly possible...

It's always been my credo to correct the data as early on in the process as follows, rather than try to catch it at the end.

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

 

[janetlyn]

You make me giggle at the end of a long hard day. Thanks. Actually, when I first found out I could just link the Excel data to Access, I set up one page with all the numbers that needed to go to Access (Accesssheet). Each of these numbers had a link that pulled a number from a bunch of different spreadsheets. That did not work. Because...about 1/2 of the sheets are constantly having lines added to them either at the bottom or the middle. I could not get the Accesssheet (looks like I'm from Texas) to follow the totals as they got moved down or sideways. However, if you can give me some help I would sure appreciate it cause you are right about the entering part.

As soon as I can cut these chains from the concrete rock tied to my feet so I can come to the surface and breath, I will start looking at that Excel validation. I have never heard of that and I thought I was an Excel &quot;expert&quot;.

HAHAHA. Have a wonderful day. Let me know your thoughts. Janet Lyn

 

[MichaelRed]

In general, you can provide specific cells of an excel sheet as &quot;NamedRange&quot;s. If Rows/Columns are added or deleted (thus MOVING the cell) the range name should track to the new location. There ARE some limitations (exceptions, such as when the cell is copied then pasted to a new location) but even there, through automation, you can check some attributes (like wheather the cell has a formula?) to be assured that you are (shoulda/woulda/coulda) getting the data desired.

Further, it is quite possible to create FORMS in Excel for the data entry, and apply most of MS A. characteristics (e.g. events, code, formatting, etc) to the form itself and the individual controls. While I remain steadfastly (NOT) a fan of spreadsheets, the use of forms can make them look and feel a bit more like a data base as well as providing additional capability. I would still STRONGLY reccomend that you replace the spreadsheets with simple custom database apps for those who currently use Excel for data entry. Even if iit is necessary to make several (or offer 'personallity' options within a single one), a reasonably designed ap will provide mush more control over the data both in the consistient referencing of the data and it the capability to do some of the data entry V&V at the point where the data is entered.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

Actually, I started out four years ago trying to figure out how I could put all the stuff I do in Excel into Access and have it print out all my reports. I'm sure there is a way to do my biggest problem, but that is one of the things I will look into once I get my database up and running a little better. The problem being, each employee has so many hours a month. Those hours are either coded to a specific project, General, Marketing, Vacation, or Sick leave. To determine the actual cost of those hours I have a formula to determine a multiplier. This multiplier (let's say $1.62) can change every six months. The multiplier is put into a formula based on each employee's hourly payrate (let's say $20.00). So ($20.00+($20.00 X 1.62)) is multiplied by each hour the employee worked. Since the multiplier changes every six months (maybe) and the pay rate changes once a year I could not figure out a way to come up with all my calculations in Access. For example,

JAN-JUN, 2001, Pay Rate Employee A $20.00; Multiplier $1.62
JUL-DEC, 2001, Pay Rate Employee A $20.00; Multiplier $1.65
JAN-JUN, 2002, Pay Rate Employee A $25.00; Multiplier $1.70
JUL-DEC, 2002, Pay Rate Employee A $25.00; Multiplier $1.60

I could not figure out (I probably could but was not and probably am still not knowledgeable enough about access) how to tell it to only take those hours from Jan-Jun 2001, go find the multiplier for the same time period, and find Employee A's rate of pay for the same time period. Then for each project the person worked on for that time period, come up with the cost based on the formula above.
And, this would go back to 1998 and would continue on ad- nauseum for old and new employees.

Maybe that will fire up your brain. Keep coming back with the ideas. Your killin' me Smalls. Actually I appreciate all this because I know I can do it and you have given me some fantastic ideas just in this thread alone. Thanks so much. JL

 

[MichaelRed]

The table would look something like unto:


EmpId (Pk / Fk to calcs)
Type [Pay | Multiplier]
StartDt (Start Date for this Change)
EndDt (End Date for this Change, leave Blank for current)

Or course, the [EndDt] is not strictly necessary, since it must always either be empty or the same as the [StartDt of the following change of that type - 1), but for those who are less experienced, it may be easier ot manipulate the &quot;Interval&quot; for each of the changes.

For any given date, element, The Employee Id, the Type ([Pay | Multiplier] and Between [StartDt] and [EndDt] provides the necessary and sufficient information.

Backing way far up,

There is never time to do it right, but theter is ALWAYS time to do it over!!!





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

e.g.:

The Table
StrtDt EndDt ItemType NewVal EmpId

1/1/2001	12/31/2001	Pay	$20.00	A
1/1/2001	6/30/2001	Mult	$1.62	A
1/1/2002	12/31/2002	Pay	$25.00	A
1/1/2002	6/30/2002	Mult	$1.70	A
7/1/2002	12/31/2002	Mult	$1.60	A
7/1/2001	12/31/2001	Mult	$1.62	A

The Query:

SELECT tblChangeCharges.EmpId, tblChangeCharges.StrtDt, tblChangeCharges.EndDt, tblChangeCharges.ItemType, tblChangeCharges.NewVal
FROM tblChangeCharges
WHERE (((tblChangeCharges.EmpId)=[MyEmpId]) AND ((tblChangeCharges.StrtDt)<=[MyDt]) AND ((tblChangeCharges.EndDt)>=[MyDt]))
WITH OWNERACCESS OPTION;

The Results:
EmpId StrtDt EndDt ItemType NewVal

A	1/1/2001	12/31/2001	Pay	$20.00
A	7/1/2001	12/31/2001	Mult	$1.62

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[janetlyn]

You are just bound and determined to make me do this right. I cannot figure out what the Pk and Fk stand for. Also, is the New Val (Pay/Mult) a text field or a yes/no field?

The rest looks great. Can't wait to get started.

BTW: I just found out that the following will fix my code from the first question above?

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

The CLng was what was given to me to add, although I have no idea what it stands for. I do know that I needed to make my strings into numbers and I think that is what the CLng is doing.


Sorry I can only vote for you once. You deserve much more for sticking it out this long. Keep this thread tagged cause I am sure I will have some more to ask, later when I have time to work on it again. Janet Lyn

 

[WildHare]

JL, I tend to agree with [red]Mike[/red], as usual - this really is a database app. Y'all think General Motors does their payroll out of 1-2-3?...

PK : Primay Key
FK : Foreign Key (non-key field that is the KEY field in another table.

CLNG : Character-to-Long(integer)data conversion function, as you have correctly surmised. One of the &quot;Witchs' Brew&quot; of conversion functions every database has, to correct fuzzy-headed wrong-thinking at data inception time (e.g. the time 4 years ago when the summer intern built your company's A/R system in Excel)

JMH







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]

'nuff said?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TekKnowGeek]

Clng == Convert to Long

Think.
Learn.
Know.
Live.