Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:



I keep getting an error saying invalid syntax: Your may have entered an operand without an operator? What am I missing in my formula?

Thanks,
Chad

Hi

Welcome to UA (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

First, you can't filter your data (that I'm aware of anyway) directly as you're trying to do with this [Supervisor]=”Lenny Lamatrice", and second can you say what DT Reason 1 and DT Reason 2 are? Are they seperate fields in the same table ?. If they are, then I'm guessing that you may also have a structure probelm - but without a bit more info I can't be sure...........

DT Reason 1 and DT Reason 2 and DT Maintenance are all times a machine is down for repair and yes they are are seperate fields.

If they're all seperate fields in the same table, then you have what's known as repeating groups which violates table design (known as normalisation). What happens, for example, if you want to add DT Reason 3 and DT Reason 4 and DT Cleaning etc etc.

As you currently have it, you'd have to add new field(s) to your table, rewrite all of your queries/forms/reports/modules to accommodate the new fields - in other words, a nightmare. Those fields need to be in their own table (called something like tblDownTimeReasons) which you'd relate to your machine table, then you can have any number of reasons for machine downtime (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Normalising your data can be challenging to grasp at first, but it's the foundations on which your database is built on and needs to be correct before you go any further, or you're just asking for trouble as you develope your app.

Here's a few links to give you a bit of reading.....have a look through them and then post back with any questions you may have

Good luck with your project (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics

Im new to access so bere with me. Everything in the report is stored information in a table. I just want to show a percent from the information that was brought up on the report. here is what my rpt looks like for one shift in the attachment.



REG= DT REGULAR
MAINT=DT MAINTENANCE
DT 1= DT REASON 1
DT 2= DT REASON 2

This is just one shift. the report will have 3 shifts a day and the report is usually brought up for a weeks worth.

Edited by: oxicottin on Sat Jun 9 10:46:26 EDT 2007.

Don't worry about being new to Access - everybody has to start somewhere (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Unfortunately, seeing the final output (ie the report that you've posted) doesn't help to get to your table structure.

As my previous reply, if you're storing your data in seperate fields in the same table then you have a structure problem that needs addressing before going any further. Can you post a list of your table fields, or better still a copy of your db (zipped to less than 500k and no sensitive data) so that we can help further

Here is a striped down version of my database. The rpt_WeeklyProductionReport is the report im working on. Its the middle text box at the top that says Total Delay. The frmMaindB is my main form and the button at the bottom that says report switchboard will bring up a menu the button that brings up the report in question is the one that says Weekly Production Report. Use the date range of 6/1/7 to 6/6/7 due to I had to delete all records to make this smaller.

Hi

Just taken a look at your db for you. Have you read any of the links I posted for you...?

IMHO sorry to say that you have major structure problems with all of your tables and they need addressing before you go any further...........

I'd strongly suggest that you do a bit of reading, have a re-think on how you're setting things up and post back in a new thread with any questions you may have - there's always a helping hand at UA to help you along.

Sorry to sound negative, but I can't stress strongly enough how important it is to get your structure correct before you start building forms/reports etc..........

I understand that working with Access can be overwhelming in the beginning. However, it will not benefit you to have someone hand you the complete solution without some effort on your part to understand the proposed solution and to try to implement it properly.

One of the best ways to gain knowledge is trial and error. Try to analyze the advice and adapt it to your application... if/when you get stuck or need clarification, ask questions...Utter Access’ members will happily help you through it...

I have to get this done now it cant wait. This database has been used since 2004 with no problems. Im sure the structure is crappy but I can repair that later. I will make a copy and try to fix the structure by reading your links but this doesnt solve my issue as of now. Any sugestions that are positive? I really do appreciate your help and I will give ntne normazation a try but as of now im in a bind with this.

Thanks,
Chad

Edited by: oxicottin on Sun Jun 10 7:21:58 EDT 2007.

Edited by: oxicottin on Sun Jun 10 7:22:45 EDT 2007.

Most experienced posters here at UA will tell you that you can "cover up" most table structure problems by throwing enough code at them, but it is rarely worth the effort, and with time the problems with the application just get progressively harder to fix via code (and said code usually gets pretty messy, as well). It is almost always much better to correct table structure problems first.

<<This database has been used since 2004 with no problems>>.......

Just goes to demonstrate how robust and somewhat forgiving Access really is

I'm really reluctant to suggest a solution for the above reasons, but as long as you promise to normalise your data I guess you could try the following to get you going........

In the On Format event procedure of the section of your report you need the calculations......



***Untested and air code + name of supervisor is hard coded***

You may need to play around with the SQL and/or the calculation, but something along those lines may be what you need

Its great that your helping me and it is highly appreciated but I have no clue what you just wrote. I understand where it goes but thats about it. I looked threw the VBA Code you wrote and I only see one supervisors name but I have to do this for each supervisor. Im confused on how this will fix my situation? I read on the internet about normalising my data but im going to have to read further because thats sumwhat fuzzy as of now. But I will learn just like im learning access. I was pushed into learning access because nobody at work would tackle this task. I think I have moved a long way by just reading articals and tutorials. I just dont understand how this code is going to allow me to total whats on my report in a text box?

Thanks,
Chad

Errrm..the code I suggested definitely wont fix your problem. As I mentioned in the 'disclaimer', the supervisors name is hard coded. I was just going by the expression you posted in your original post - I should really have looked at the report in the db you posted - which I now just have done. Sorry about that........

I totally understand your frustration, particularly as you've been the one pushed into taking on this 'challenge', and if it's any consolation (which I guess it isn't), given the state of the tables I can understand why nobody else would want to do it.

Unfortunately, having had another quick look at the db you're working with, you really are pretty much up the creek without a paddle until some sort of order is made of the data/structure. If I were you, I'd have a word with whoever pushed you into taking on this db and explain that, whilst you're happy to continue working on the project, it wont be a quick fix and that they'll have to bear with you as need to do some substantial re-structuring. Explain to them that once done, they'll have a fully functioning, efficient, manageable and stable database where they'll be able to produce accurate and meaningful stats/reports/analysis on their data very much more easily than at present - as you've discovered!!!

It really would be like using a sticking plaster to even begin to offer a solution for you right now, and unfortunately I'm going to be tied up (not literally I hasten to add!!) for the next couple of days to be able to devote the time needed to help you. I'll put a call out and see if anyone else can jump in for you, if not I'll be able to help you re-structure in a few days.......

Good luck.......... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

I asked my brother for some help and He got it somewhat working and he asked me to post this question.

I have an expression on a report that is not being saved in a table. In the control source I have the following...
=[Expr1]/[EMPLOYEE TIME]

I want to be able to single out a specific supervisor in that report. For example, Larry shows up 4 times on the report. I want Expr1 summed up and EMPLOYEE TIME summed and then divided by each to only have Larry's data. how do I incorporate the Supervisor aspect into my expression without creating another query?

Any sugestions? Thanks!!!!

You should not save the result of the expression to a table ...
The result should be "dynamic" by calculating the result in real time.
Storing the result would place the value in jeopady should any value used in the expression get changed for any reason.

RDH

I think this is what you want.
It's really just a guess.

=Sum(IIf([Supervisor]='Lenny Lamatrice',[DT REGULAR],0)) / Sum(IIf([Supervisor]='Lenny Lamatrice',[Employee Time],0))

I can't comment on your table structure because I don't have a
clue what you are modeling or what the fields represent.

Putting a description of the fields in the design view of your tables would
go a long way toward helping others understand what you're doing.

I had to 'crunch' the report format to get it to show all even with Legal size form. But...

I ran the report, and I do not get any errors at all! -- See attached.

Is it possible this is a "Data Issue" and not a "Code Issue"?

We all know this MDB has major design issues, but the stripped-down MDB posted doesn't give any errors, and you mentioned alot of data was stripped -- the culprit giving the errors seems likely to be within the data that was stripped...

Just my 2.5 cents worth...

I only get no errors when the database is running. I works great! My boss wanted me to add the Total Delay to the report as a visiual. It isnt stored anywhere its just for show because he prints this out every week on 11X17 paper thats why you probily get messed up sheets as well. I am trying the formula that Ace posted because thats what im looking for. I will let everyone know the results in a few...

Thanks,
Chad

Hi Chad,

I plugged Ace's code in the Control Source, and it does seem to work as you desire. Modified MDB attached.

Also, I took the liberty of reformatting the report to fit on 8.5 x 11 paper -- Less color + less paper = Cost Savings to USG! The DT1/DT2 row only prints if data is present for those values. And the Dates at top right always print as MMMM dd yyyy no matter how it is entered in the parameter inputs.

At work they seem to come to me to design forms/reports (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif) .

You are the best!!! This is the greatest thing... I have been trying to fix this for weeks, reading searching and asking questions. I should have tried this forum first!!! Everyone here that resoned thanks as well it is very appreciated. Fdcusa, by looked at the database what else do you see thats wrong or needs worked on? I have strugled with this thing for so long thats why alot of the database needs normalising. I was self tought over the net and learned what I could. I think I have come a long way. Thanks again and please give me some sugestions on what to fix because im always eager to learn since im the only one at our plant that has an idea on how to do some of this stuff.. I wish I could upload the whole thing but compressed its 656kb which is over limit!

OH I forgot I had to add DT MAINTANCE,DT Reason 1 and DT Reason 2 into the formula because those are delays occured just like DT REGULAR.

=Sum(IIf([Supervisor]='Chad Zablackas',[DT REGULAR]+[DT Reason 1]+[DT Reason 2]+[DT MAINTENANCE],0))/Sum(IIf([Supervisor]='Chad Zablackas',[Employee Time],0))

Thanks,
Chad


Edited by: oxicottin on Sun Jun 10 23:07:59 EDT 2007.

Did you run compact/repair before you zipped it?