Fathan,
A while back you requested that I uploaded the Excel spreadsheet that queried data from Access 2007 so you could review it and provide sample code for this function. I uploaded the file, but never heard back from you. Did you ever look at it?
v/r,
James Eggert
--- In MS_Access_Professionals@yahoogroups.com, "Fathan" <fathan02@...> wrote:
>
>
> Hi James,
>
> You can write vba code in excel to update access database field based on the yellow column in excel.
>
> I unable to read your excel file since I am using ms excel 2002. If you can send me your excel file in 2002 format maybe I can show you the sample code to save access from excel with vba code.
>
> Best regards,
> Fathan Haras
>
> -----Original Message-----
> From: j1eggert <no_reply@yahoogroups.com>
> Sender: MS_Access_Professionals@yahoogroups.com
> Date: Tue, 28 Dec 2010 19:56:24
> To: <MS_Access_Professionals@yahoogroups.com>
> Reply-To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Convert Excel Formula to a Query Expression
>
> Hi Clive,
>
> I appreciate the help. The columns in green are required fields that one of the agencies need in order to provide the data for the columns in yellow.
>
> One query in Access provides the data in the green columns while the yellow columns are imported into a table in Access once the Excel spreadsheet is returned by the agency. The reason the data is filled out in a Excel spreadsheet is because the agency will not have access to the Access database. The requirements from the customer are very unusual and difficult to work around.
>
> Regards,
> James Eggert
>
> --- In MS_Access_Professionals@yahoogroups.com, "horastacatto" <zctek@> wrote:
> >
> > Hi James,
> >
> > I've had a look at the IPR_Form.xlsx file.
> >
> > Don't import the field 'Qty_Allocated_and_ROM_Delta'
> > to Access. It does not make sense to try. It would
> > import OK but as a numeric field. The work can be
> > done in Access by adding one extra field to a query.
> >
> > When you go through the Import steps, set the field
> > to 'Do Not Iport'.
> >
> > If you want to show in Access the value of the
> > missed field, just create a query with all the fields
> > plus,
> > "[YourTable].[SumOfQtyAllocated] +_
> > [YourTable].[ROM_Delta],0) AS_
> > Qty_Allocated_and_ROM_Delta;"
> >
> > That gets the data into Access. What you do next I
> > don't know. To provide a solution we would need to
> > to know how the Data Flow operates. Where the data
> > sources come from and what type of file or form.
> > Also where the data goes from each component and
> > when and where it gets updated.
> >
> > Hope that helps.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, j1eggert <no_reply@> wrote:
> > >
> > > Hello Fathan,
> > >
> > > The Access data is manually entered using a form. I have unusual business rules and constraints from the customer. Not all the data is provided and/or generated by the organization I support. The Access database I am developing will be uploaded to SharePoint, but their are other agencies that either provide data or use the data in the database that will not have access to the SharePoint site. So, I have to find unique ways of accomidating these other agencies requirements.
> > >
> > > I have a degree in database administration and understand the need for normalization and functional dependencies. I think my design is the best that can be done to normalize the tables, but the contraints make it impossible to normalizt to the 3rd level. In addition, I have not done this in a while (includng programming) so I am taking some refresher courses and reading books.
> > >
> > > Regards,
> > > James Eggert
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Fathan" <fathan02@> wrote:
> > > >
> > > > Hi James,
> > > >
> > > > Understand you've got your Excel Data from Access Data (using import wizard) but how did you get your access data ?
> > > >
> > > > Best regards,
> > > > Fathan Haras
> > > >
> > > > -----Original Message-----
> > > > From: j1eggert <no_reply@yahoogroups.com>
> > > > Sender: MS_Access_Professionals@yahoogroups.com
> > > > Date: Tue, 28 Dec 2010 15:57:56
> > > > To: <MS_Access_Professionals@yahoogroups.com>
> > > > Reply-To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Convert Excel Formula to a Query Expression
> > > >
> > > > Hello Crystal,
> > > >
> > > > 1. The data goes back into one table. The table was created from the import wizard.
> > > > 2. No keys. I tried to use a column from the import side of the spreadsheet as a key, but it would not allow for this. And, if I try to create a key or allow the wizard to create a key the new table will revert back to no primary key.
> > > > 3. I will upload the Excel Spreadsheet for you to review.
> > > >
> > > > Thanks,
> > > > James Eggert
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@> wrote:
> > > > >
> > > > > Hi James,
> > > > >
> > > > > bringing the data back in is more complex than sending it out ...
> > > > >
> > > > > 1. how many tables does the data go back into?
> > > > > 2. please list the columns that have keys
> > > > > 3. please list your relevant data structure
> > > > >
> > > > > thanks
> > > > >
> > > > >
> > > > > Warm Regards,
> > > > > Crystal
> > > > >
> > > > > *
> > > > > (: have an awesome day :)
> > > > > *
> > > > >
> > > > >
> > > > > --- On Tue, 12/28/10, j1eggert wrote:
> > > > > Crystal,
> > > > >
> > > > > I did what you asked, broke the link, sent the Excel spreadsheet out for the additional data and then did a save as and replaced the previous Excel spreadsheet, what next?
> > > > >
> > > > > Thanks,
> > > > > James Eggert
> > > > >
> > > > > ---Crystal <strive4peace> wrote:
> > > > > >
> > > > > > Hi James,
> > > > > >
> > > > > > Please re-read what I have already written about this. One of the things I told you to do is BREAK your links to the data in Excel BEFORE you send the document out for more information. Break the link and do a SAVE AS for your customer. I believe you might be neglecting to do this.
> > > > > >
> > > > > > One (or more) of the columns for your Excel data should be the key(s) you will need to link the new data back to Access. They may be hidden columns in Excel.
> > > > > >
> > > > > > As for using the same query to import and export... I would guess that the import should be done with code...
> > > > > >
> > > > > > Warm Regards,
> > > > > > Crystal
> > > > > >
> > > > > > *
> > > > > > (: have an awesome day :)
> > > > > > *
> > > > > >
> > > > > >
> > > > > > --- On Mon, 12/27/10, j1eggert wrote:
> > > > > >
> > > > > > Hi Crystal,
> > > > > >
> > > > > > No need to apologize, because I do believe that part of the problem has been resolved. I created an Excel Query and export from Access to Excel (from Excel), which works without errors. Since the customer wants the data to be replaced with new data every two weeks I replace the old data with the most recent by using the "Date ()" and set the criteria "Between Date () and Date ()-14" in Access. Let me know if this helps. I don't need to do anything manually in Excel this way. All of the headers and formatting remain the same when I refresh the data in Excel. If you think this is a good method then I can explain how the next step works.
> > > > > >
> > > > > > Regards,
> > > > > > James Eggert
> > > > > >
> > > > > > --- Crystal <strive4peace> wrote:
> > > > > > >
> > > > > > > Hi James,
> > > > > > >
> > > > > > > I read other posts on this and thought you had it figured out, sorry. Now that you have more information, please summarize the problem (even though some of what you say will repeat what you have already said).
> > > > > > >
> > > > > > > Describe the method(s) you are trying to implement, and answer every question I have previously asked you. Post any relevant code, data structure, and SQL, thanks :)
> > > > > > >
> > > > > > > Warm Regards,
> > > > > > > Crystal
> > > > > > >
> > > > > > > *
> > > > > > > (: have an awesome day :)
> > > > > > > *
> > > > > > >
> > > > > > >
> > > > > > > --- On Mon, 12/27/10, j1eggert wrote:
> > > > > > >
> > > > > > > Crystal,
> > > > > > > Did you get my last post? I hope you didn't forget about me :(
> > > > > > >
> > > > > > > Thanks,
> > > > > > > James Eggert
> > > > > > >
> > > > > > > --- Crystal <strive4peace> wrote:
> > > > > > > >
> > > > > > > > hi James,
> > > > > > > >
> > > > > > > > you're welcome :)
> > > > > > > >
> > > > > > > > Perhaps you are wasting your time trying to get the calculation in the query, perhaps it should just be added as another column in Excel once the data is there. Since you are sending the sheet out to get more data in, you definitely want to break the links to the data in Access -- meaning, import a static copy of the data that will not be updated once it is in Excel.
> > > > > > > >
> > > > > > > > If you do this process a lot, you will want to use code ... you can use Excel to pull the data from Access, as Fathan has given you the beginning code for ... or you can use Access to push a static copy of the data into Excel, and then use code to create the rest of the column headers to fill, format the cells, and create forumulas. This programming is probably over your head right now.
> > > > > > > >
> > > > > > > > So, baby steps first:
> > > > > > > >
> > > > > > > > Try exporting the query from Access to Excel (see if you can push it without errors -- provided it works in Access). Then manually insert the additional columns and formulas in Excel. Document the extra steps you need to take.
> > > > > > > >
> > > > > > > > As for reading the data back into Access... we can address that after the export is working right ;)
> > > > > > > >
> > > > > > > >
> > > > > > > > Warm Regards,
> > > > > > > > Crystal
> > > > > > > >
> > > > > > > > *
> > > > > > > > (: have an awesome day :)
> > > > > > > > *
> > > > > > > >
> > > > > > > >
> > > > > > > > --- On Wed, 12/22/10, j1eggert wrote:
> > > > > > > >
> > > > > > > >
> > > > > > > > Hi Crystal,
> > > > > > > >
> > > > > > > > Thank you for the quick response! I have a unique requirement from a customer. Some of the data provided to the customer comes from outside the organization via Excel.
> > > > > > > >
> > > > > > > > The customer uses an Excel spreadsheet as a form. The first 5 columns are imported into Excel, from Access as a query. The other columns are filled out my another organization based on the imported data provided. When the Excel spreadsheet comes back completed I try to import the old and new data into an Access table. Basically, the same Excel spreadsheet is importing and exporting data. This may be causing some of these errors. I am thinking of another way to handle this without importing and exporting data from the same spreadsheet.
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > James Eggert
> > > > > > > >
> > > > > > > >
> > > > > > > > --- Crystal <strive4peace> wrote:
> > > > > > > > >
> > > > > > > > > Hi James,
> > > > > > > > >
> > > > > > > > > make sure you do not have the parameters reversed ... if you do not, then:
> > > > > > > > >
> > > > > > > > > if yourfield is a number, try --> ..., "no value", yourfield + 0
> > > > > > > > > if yourfield is text, try --> ..., "no value", yourfield & ""
> > > > > > > > >
> > > > > > > > > if the Name of yourfield has spaces or special characters (anything that is not a letter or number or underscore), enclose it in [square brackets].
> > > > > > > > >
> > > > > > > > > --> [your field]
> > > > > > > > >
> > > > > > > > > If yourfield starts with anything other than a letter, then rename it.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > if there are still issues, then make calculated columns with each of your expressions (the one for TRUE and the one for FALSE)
> > > > > > > > >
> > > > > > > > > delete the column with ... IIF (yourfield IS NULL
> > > > > > > > >
> > > > > > > > > if both of the expressions render, then you know that you cannot use IS NULL, or something is wrong with the whole expression ... are the parentheses balanced? Please post your SQL
> > > > > > > > >
> > > > > > > > > ~~~
> > > > > > > > >
> > > > > > > > > Are you using the code that Fathan gave you to put the table in? If not, how are you getting to the data?
> > > > > > > > >
> > > > > > > > > what version of Excel are you using?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Warm Regards,
> > > > > > > > > Crystal
> > > > > > > > >
> > > > > > > > > *
> > > > > > > > > (: have an awesome day :)
> > > > > > > > > *
> > > > > > > > >
> > > > > > > > > --- On Wed, 12/22/10, j1eggert wrote:
> > > > > > > > >
> > > > > > > > > Hi Fathan,
> > > > > > > > >
> > > > > > > > > I tried the formula you provided, which seems to work, but when I create an Excel query to pull the data into it I get the following error message: "Too few parameters. Expecting 2." Not sure why this error popped up.
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > James Eggert
> > > > > > > > >
> > > > > > > > > --- "Fathan" <fathan02@> wrote:
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Hi James,
> > > > > > > > > >
> > > > > > > > > > Sorry, you are correct, I found nz is not work for excel, please try this in your query:
> > > > > > > > > >
> > > > > > > > > > Iif(yourfield IS NULL, "no value",yourfield)
> > > > > > > > > >
> > > > > > > > > > Best Regards,
> > > > > > > > > >
> > > > > > > > > > Fathan Haras
> > > > > > > > > >
> > > > > > > > > > -----Original Message-----
> > > > > > > > > > From: j1eggert
> > > > > > > > > >
> > > > > > > > > > Hi Fathan,
> > > > > > > > > > Of course my first try was creating Excel Query (ODBC, which resulted in the problem with "Nz". I tried to just import from Access to Excel, but the query does not even show up on the list and I think its because of the formula in the query. However, I am not sure what you mean by using ADO, could you be a little more specific?
> > > > > > > > > >
> > > > > > > > > > Thanks,
> > > > > > > > > > James Eggert
> > > > > > > > > >
> > > > > > > > > > --- "Fathan" <fathan02@> wrote:
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > Hi James,
> > > > > > > > > > >
> > > > > > > > > > > You can save your query in Access database and call it using select query in excel (using ADO).
> > > > > > > > > > >
> > > > > > > > > > > Hopefully this suffices your question.
> > > > > > > > > > >
> > > > > > > > > > > Best regards
> > > > > > > > > > > Fathan Haras
> > > > > > > > > > >
> > > > > > > > > > > -----Original Message-----
> > > > > > > > > > > From: j1egger
> > > > > > > > > > >
> > > > > > > > > > > Hi Crystal,
> > > > > > > > > > >
> > > > > > > > > > > I used the "nz" in the Access query and it worked great; however, I need to use this field in a Excel query for a report. When I try to bring the Access query over it does not like the "nz" in the formula expression. Is their a substitute that both Access and Excel can be happy with?
> > > > > > > > > > >
> > > > > > > > > > > Very Respectfully,
> > > > > > > > > > > James Eggert
> > > > > > > > > > >
> > > > > > > > > > > --- Crystal <strive4peace> wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > hi Fathan
> > > > > > > > > > > >
> > > > > > > > > > > > yes that is good, thanks for pointing out Value -- forgot about that
> > > > > > > > > > > > -- but wrapping L2 with Value in the equation is not necessary because it has already been tested; this is all you need:
> > > > > > > > > > > >
> > > > > > > > > > > > =IF( value(L2)=0, 0, value(N2)/L2 )
> > > > > > > > > > > >
> > > > > > > > > > > > The only reason I can think of to return text instead of a number (in the original eqn) if the equation cannot be evaluated is to count (or only consider) numeric values somewhere else
> > > > > > > > > > > >
> > > > > > > > > > > > Warm Regards,
> > > > > > > > > > > > Crystal
> > > > > > > > > > > >
> > > > > > > > > > > > *
> > > > > > > > > > > > (: have an awesome day :)
> > > > > > > > > > > > *
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --- On Tue, 12/21/10, Fathan wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > //Revised//
> > > > > > > > > > > >
> > > > > > > > > > > > Hi Crystal,
> > > > > > > > > > > >
> > > > > > > > > > > > How about this formula:
> > > > > > > > > > > >
> > > > > > > > > > > > =IF( value(L2)=0, 0, value(N2)/value(L2) )
> > > > > > > > > > > >
> > > > > > > > > > > > Regards
> > > > > > > > > > > > Fathan
> > > > > > > > > > > > Fathan Haras
> > > > > > > > > > > >
> > > > > > > > > > > > -----Original Message-----
> > > > > > > > > > > > From: Crystal
> > > > > > > > > > > >
> > > > > > > > > > > > Hi James,
> > > > > > > > > > > >
> > > > > > > > > > > > here is how I might have written your Excel formula:
> > > > > > > > > > > >
> > > > > > > > > > > > =IF( L2+0=0, 0, SUM(N2) /L2 )
> > > > > > > > > > > > or
> > > > > > > > > > > > =IF( L2+0=0, 0, N2/L2 )
> > > > > > > > > > > >
> > > > > > > > > > > > or, since I have read that an inequality is faster to evaluate:
> > > > > > > > > > > > =IF( L2+0<>0, SUM(N2) /L2 ,0 )
> > > > > > > > > > > >
> > > > > > > > > > > > or
> > > > > > > > > > > >
> > > > > > > > > > > > =IF( L2+0<>0, N2/L2 ,0 )
> > > > > > > > > > > >
> > > > > > > > > > > > if L2 is empty or contains text, the condition to test will result in 0, which means that the NUMBER (as opposed to "0%" text) 0 (zero) will be returned -- since the cell is formatted. As long as L2 is a non-zero number, the division can happen without an error. The reason to wrap N2 with the Sum function is in case it contains text instead of a number. If that will never happen, then don't use Sum.
> > > > > > > > > > > >
> > > > > > > > > > > > if L2 or N2 contains a formula as opposed to source values, I would use IsError
> > > > > > > > > > > >
> > > > > > > > > > > > =IF( IsError(N2/L2), 0 N2/L2 )
> > > > > > > > > > > >
> > > > > > > > > > > > the reason I would not use this to begin with is because forcing an error situation might slow it down more than comparing the value -- but what is a few nanoseconds to a human ;)
> > > > > > > > > > > >
> > > > > > > > > > > > Oddly enough, these 2 are not the same:
> > > > > > > > > > > > Sum(N2)
> > > > > > > > > > > > N2 + 0
> > > > > > > > > > > >
> > > > > > > > > > > > while adding 0 is ok for the condition to test, it is not ok to use in the formula ...
> > > > > > > > > > > >
> > > > > > > > > > > > Bill "MrExcel" Jelen (bjele123) and Mike Girvin (ExcelisFun) both have > 1000 Excel video tutorials on YouTube. If you go to my channel, I am subscribed to both of them so you can get to their channels from mine.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > http://www.YouTube.com/LearnAccessByCrystal
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > right now, I have a playlist featured on my channel with Access 2010 videos done by others -- if you want to see the videos I have uploaded for Access, click Uploads or choose my "Learn Access By Crystal" Playlist.
> > > > > > > > > > > >
> > > > > > > > > > > > Warm Regards,
> > > > > > > > > > > > Crystal
> > > > > > > > > > > >
> > > > > > > > > > > > *
> > > > > > > > > > > > (: have an awesome day :)
> > > > > > > > > > > > *
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --- On Tue, 12/21/10, j1eggert wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > Hello Crystal,
> > > > > > > > > > > >
> > > > > > > > > > > > The customer used the IsError function on a column where the data type is set to percentage. This is where I was confused, since the cells were set to give a percentage (not text) then why use IsError?
> > > > > > > > > > > >
> > > > > > > > > > > > Very Respectfully,
> > > > > > > > > > > > James Eggert
> > > > > > > > > > > >
> > > > > > > > > > > > --- Crystal <strive4peace2008> wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > Hi James,
> > > > > > > > > > > > >
> > > > > > > > > > > > > using the Sum function on a single cell is a way of circumventing errors with text in the numerator since Sum will always return a number if it can ... but it would have to be Sum(L2)/N2 to make any sense. Using IsError (in Excel) eliminates the need to use Sum
> > > > > > > > > > > > >
> > > > > > > > > > > > > Access has NZ but Excel does not
> > > > > > > > > > > > >
> > > > > > > > > > > > > warm regards,
> > > > > > > > > > > > > Crystal
> > > > > > > > > > > > >
> > > > > > > > > > > > > (: have an awesome day :)
> > > > > > > > > > > > >
> > > > > > > > > > > > > --- On Mon, 12/20/10, j1eggert wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > I am not as familiar with Excel as I am Access either; and, I was not understanding the expression either. But, your solution was very helpful.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks,
> > > > > > > > > > > > > James Eggert
> > > > > > > > > > > > >
> > > > > > > > > > > > > --- Duane Hookom <duanehookom@> wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I don't understand the original Excel expression use of Sum() since there is no range of multiple cells. The IsError just captures errors caused by a denominator of 0. I don't work in Excel as much as Access but IMO there are three mistakes in:
> > > > > > > > > > > > > > =IF(ISERROR((N2/L2)), "0%", Sum(N2/L2))
> > > > > > > > > > > > > > Maybe it's just a matter of programming style.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Duane Hookom
> > > > > > > > > > > > > > MS Access MVP
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Duane,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > The formula you provided seems to work fine, but I am still don't understand the customer's logic by using the original formula I email you (IF IsError).
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Regards,
> > > > > > > > > > > > > > James Eggert
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > James,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Does this expression work for you?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > PercentCompleted: IIf(Nz(QtyAllocatedROMDelta,0)=0,0,QtyCompleted/QtyAllocatedROMDelta)
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > If not, please provide your SQL statement as well as a sampling of QtyCompleted and QtyAllocatedROMDelta with the displayed, wrong results.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Duane Hookom
> > > > > > > > > > > > > > > MS Access MVP
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > From: no_reply@yahoogroups.com
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > This was Crystal's original response:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > "IsError is probably being tested because division by zero is not allowed. Without the SUM function, the logic transfers to this:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > IIF( nz( [field_L2],0 ) =0, 0, nz([field_N2],0) / [field_L2] )
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > WHERE
> > > > > > > > > > > > > > > this equation is a calculated field in a query or after = in the ControlSource of a textbox on a form or report.
> > > > > > > > > > > > > > > The Format property is set to show percentage"
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Then John responded with this suggestion:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > "And I would do:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > =Sum(IIf([L2] = 0, 0, [N2]/[L2])"
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I don't like using ISError either, but this was the original formula the customer used. I don't think the customer cares as long as it provides the desired end result.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Thanks,
> > > > > > > > > > > > > > > James Eggert
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > I expect the Excel expression is confusing most of us (at least me). Why is there even a Sum() and not simply:
> > > > > > > > > > > > > > > > =IF(L2=0,0,SUM(N2/L2))
> > > > > > > > > > > > > > > > I would have removed the quotes and % from the "0%" since you want formatted applied by the formatting of the cell. I generally don't like using IsError().
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Try:
> > > > > > > > > > > > > > > > PercentCompleted: IIf(Nz(QtyAllocatedROMDelta,0)=0,0,QtyCompleted/QtyAllocatedROMDelta)
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Duane Hookom
> > > > > > > > > > > > > > > > MS Access MVP
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > From: no_reply@yahoogroups.com
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Hello Duane,
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > The original formula came from the customer's Excel Spreadsheet: =IF(ISERROR((N2/L2)),"0%",Sum(n2/L2)). I need to convert this formula to a similar function in an Access query. Without the =Sum(...) the formula works, but does not provide the duplication of the original formula in Excel. Let me know if this helps.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Regards,
> > > > > > > > > > > > > > > > James Eggert
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > You will always get this error if you have a totals query that doesn't select Group By or some aggregate function from the Totals row.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > I think the expression is wonky because of the =Sum(...). What are you actually attempting to calculate? I think you want something like:
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > PercentCompleted: IIf(Nz(Sum([QtyAllocatedROMDelta]),0)=0,0,Sum([QtyCompleted])/Sum([QtyAllocatedROMDelta]))
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > It would help if you provided the entire SQL view.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Duane Hookom
> > > > > > > > > > > > > > > > > MS Access MVP
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > From: no_reply@yahoogroups.com
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Hello John,
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Sorry, I did not do the formula correctly, here is the correction:
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > PercentCompleted: IIf(nz([QtyAllocatedROMDelta],0)=0,0,nz([QtyCompleted],0)/[QtyAllocatedROMDelta])=Sum(IIf([QtyAllocatedROMDelta]=0,0,[QtyCompleted]/[QtyAllocatedROMDelta]))
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > I am still getting the same error message.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Regards,
> > > > > > > > > > > > > > > > > James Eggert
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <JohnV@> wrote:
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > And I would do:
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > =Sum(IIf([L2] = 0, 0, [N2]/[L2]))
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > John Viescas, author
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > -----Original Message-----
> > > > > > > > > > > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > > > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
> > > > > > > > > > > > > > > > > > Sent: Friday, December 17, 2010 8:21 PM
> > > > > > > > > > > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > > > > > > > > > > Subject: Re: [MS_AccessPros] Convert Excel Formula to a Query Expression
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > Hi James,
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > IsError is probably being tested because division by zero is not allowed.
> > > > > > > > > > > > > > > > > > Without the SUM function, the logic transfers to this:
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > IIF( nz( [field_L2],0 ) =0, 0, nz([field_N2],0) / [field_L2] )
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > WHERE
> > > > > > > > > > > > > > > > > > this equation is a calculated field in a query or after = in the ControlSource
> > > > > > > > > > > > > > > > > > of a textbox on a form or report.
> > > > > > > > > > > > > > > > > > The Format property is set to show percentage
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > Warm Regards,
> > > > > > > > > > > > > > > > > > Crystal
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > (: have an awesome day :)
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > > --- On Fri, 12/17/10, j1eggert wrote:
> > > > > > > > > > > > > > > > > > The customer has an Excel spreadsheet that I need to convert to Access. One of
> > > > > > > > > > > > > > > > > > the columns has the following formula: =IF(ISERROR((N2/L2)), "0%", Sum
> > > > > > > > > > > > > > > > > > (N2/L2)). I am not sure how to convert this into a query expression. If anyone
> > > > > > > > > > > > > > > > > > can help with this I would greatly appreciate it.
> > > > > > > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar