Coding for future month results

Below is my current code for October's reporting.  It is a project form 
showing planned progress (OctPlan), actual progress (OctAct) and via the 
attached code, an indicator and color for October (OctInd).  This code works 
great (thanks to the help on here)  However, my form shows all 12 months, so 
the code currently causes future months to indicate red.  Is there a an If 
then type statement that I can place this current code into that will 
basically say If OctAct is null then OctInd = N/A?  or is null ruined based 
on the the code?  Is there some other procedure I can try?  I've tried using 
dates to no avail.  This may be due to my lack syntax knowledge.

Private Sub PEPOctAct_AfterUpdate()
On Error Resume Next
If Me.PEPOctAct > 0.0001 Then
        Me.PEPOctAct = Me.PEPOctAct / 100
    End If
Dim dblPercentage As Double
    dblPercentage = (Me.PEPOctAct.Value / Me.PEPOctPlan.Value) * 100
    Select Case dblPercentage
        Case 80 To 95
            Me.PEPOctInd.Value = "Y"
            Me.PEPOctInd.BackColor = vbYellow
        Case Is < 80
            Me.PEPOctInd.Value = "R"
            Me.PEPOctInd.BackColor = vbRed
        Case Is >= 95
            Me.PEPOctInd.Value = "G"
            Me.PEPOctInd.BackColor = vbGreen
    End Select
   If Me.PEPOctPlan < 0.0001 Then
        Me.PEPOctInd.Value = "N/A"
    End If


-- 
qwerty
0
Utf
2/11/2008 6:09:01 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
970 Views

Similar Articles

[PageSpeed] 1

John wrote:

>Below is my current code for October's reporting.  It is a project form 
>showing planned progress (OctPlan), actual progress (OctAct) and via the 
>attached code, an indicator and color for October (OctInd).  This code works 
>great (thanks to the help on here)  However, my form shows all 12 months, so 
>the code currently causes future months to indicate red.  Is there a an If 
>then type statement that I can place this current code into that will 
>basically say If OctAct is null then OctInd = N/A?  or is null ruined based 
>on the the code?  Is there some other procedure I can try?  I've tried using 
>dates to no avail.  This may be due to my lack syntax knowledge.
>
>Private Sub PEPOctAct_AfterUpdate()
>On Error Resume Next
>If Me.PEPOctAct > 0.0001 Then
>        Me.PEPOctAct = Me.PEPOctAct / 100
>    End If
>Dim dblPercentage As Double
>    dblPercentage = (Me.PEPOctAct.Value / Me.PEPOctPlan.Value) * 100
>    Select Case dblPercentage
>        Case 80 To 95
>            Me.PEPOctInd.Value = "Y"
>            Me.PEPOctInd.BackColor = vbYellow
>        Case Is < 80
>            Me.PEPOctInd.Value = "R"
>            Me.PEPOctInd.BackColor = vbRed
>        Case Is >= 95
>            Me.PEPOctInd.Value = "G"
>            Me.PEPOctInd.BackColor = vbGreen
>    End Select
>   If Me.PEPOctPlan < 0.0001 Then
>        Me.PEPOctInd.Value = "N/A"
>    End If


If PEPOctAct is null, then you will get an error because of
the attempt to put Null into dblPercentage.

Perhaps I misunderstood what you are doing??

-- 
Marsh
MVP [MS Access]
0
Marshall
2/11/2008 7:56:12 PM
Hi Marshall - Thanks for your response.
OctAct (the item I want to allow to be null in future months) is the 
numerator.  A numerator should be able to be zero without error**.  
Unfortunately, I am not able to get Access to destinquish between zero and 
null (empty).  It treats both cases as zero in the code as posted.  But based 
on the side note, they are different.

**side note re: errors - when I remove the "On Error Resume Next" line, it 
errors out when I have zero or null in OctPlan (denominator) or null in 
OctAct (numerator) but does not error out when there is zero in OctAct.

Thanks

-- 
qwerty


"Marshall Barton" wrote:

> John wrote:
> 
> >Below is my current code for October's reporting.  It is a project form 
> >showing planned progress (OctPlan), actual progress (OctAct) and via the 
> >attached code, an indicator and color for October (OctInd).  This code works 
> >great (thanks to the help on here)  However, my form shows all 12 months, so 
> >the code currently causes future months to indicate red.  Is there is an If 
> >then type statement that I can place this current code into that will 
> >basically say If OctAct is null then OctInd = N/A?  or is null ruined based 
> >on the the code?  Is there some other procedure I can try?  I've tried using 
> >dates to no avail.  This may be due to my lack syntax knowledge.
> >
> >Private Sub PEPOctAct_AfterUpdate()
> >On Error Resume Next
> >If Me.PEPOctAct > 0.0001 Then
> >        Me.PEPOctAct = Me.PEPOctAct / 100
> >    End If
> >Dim dblPercentage As Double
> >    dblPercentage = (Me.PEPOctAct.Value / Me.PEPOctPlan.Value) * 100
> >    Select Case dblPercentage
> >        Case 80 To 95
> >            Me.PEPOctInd.Value = "Y"
> >            Me.PEPOctInd.BackColor = vbYellow
> >        Case Is < 80
> >            Me.PEPOctInd.Value = "R"
> >            Me.PEPOctInd.BackColor = vbRed
> >        Case Is >= 95
> >            Me.PEPOctInd.Value = "G"
> >            Me.PEPOctInd.BackColor = vbGreen
> >    End Select
> >   If Me.PEPOctPlan < 0.0001 Then
> >        Me.PEPOctInd.Value = "N/A"
> >    End If
> 
> 
> If PEPOctAct is null, then you will get an error because of
> the attempt to put Null into dblPercentage.
> 
> Perhaps I misunderstood what you are doing??
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
2/13/2008 5:37:03 PM
John wrote:

>Hi Marshall - Thanks for your response.
>OctAct (the item I want to allow to be null in future months) is the 
>numerator.  A numerator should be able to be zero without error**.  
>Unfortunately, I am not able to get Access to destinquish between zero and 
>null (empty).  It treats both cases as zero in the code as posted.  But based 
>on the side note, they are different.
>
>**side note re: errors - when I remove the "On Error Resume Next" line, it 
>errors out when I have zero or null in OctPlan (denominator) or null in 
>OctAct (numerator) but does not error out when there is zero in OctAct.


You really should not use On Error Resume Next.  You'll
won't know when your code falls on its face and starts
mangling data util it's too late to do anything about it.

I think(?) you can get the effect you want by using the Nz
function to convert a Null value to 0 or whatever is
appropriate to you calculation.

dblPercentage = (Nz(Me.PEPOctAct.Value,0) /
Me.PEPOctPlan.Value) * 100

-- 
Marsh
MVP [MS Access]
0
Marshall
2/13/2008 7:51:51 PM
Thanks Marshall.  The Nz did not work out.  Instead, I add a 
Case 0 To 0.01
            Me.PEPOctInd.Value = "N/A"
            'Me.PEPOctInd.BackColor = vbTBD

This will cover the situation, although not perfect.  Thanks for the time 
and attention.
-- 
qwerty


"Marshall Barton" wrote:

> John wrote:
> 
> >Hi Marshall - Thanks for your response.
> >OctAct (the item I want to allow to be null in future months) is the 
> >numerator.  A numerator should be able to be zero without error**.  
> >Unfortunately, I am not able to get Access to destinquish between zero and 
> >null (empty).  It treats both cases as zero in the code as posted.  But based 
> >on the side note, they are different.
> >
> >**side note re: errors - when I remove the "On Error Resume Next" line, it 
> >errors out when I have zero or null in OctPlan (denominator) or null in 
> >OctAct (numerator) but does not error out when there is zero in OctAct.
> 
> 
> You really should not use On Error Resume Next.  You'll
> won't know when your code falls on its face and starts
> mangling data util it's too late to do anything about it.
> 
> I think(?) you can get the effect you want by using the Nz
> function to convert a Null value to 0 or whatever is
> appropriate to you calculation.
> 
> dblPercentage = (Nz(Me.PEPOctAct.Value,0) /
> Me.PEPOctPlan.Value) * 100
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
2/20/2008 1:11:03 AM
Reply:

Similar Artilces:

Counting results of a formula
I have a column that has about 111 rows to it. In each row is a formula that will find an invoice number if it exists elsewhere. The are alpha-numeric invoice #s. I need to count the number of invoices found. If I use the COUNTA formula for that column it will count those cells that have the fomula in it but no result. I only want to count those cells that have a result from the formula. Any ideas for using Excel 2003? Thanks. =COUNTIF(G1:G111,">""") Gord Dibben MS Excel MVP On Tue, 4 May 2010 10:10:01 -0700, Nadine <Nadine@discussions.mi...

Business Type Code Vs Industry
Hello, Can someone explain the difference between Business Type Code & Industry? What is the role or purpose of Business Type code. If anyone knows of any resources that could explain the intended purpose of this and some of the other fields on the account object such as Classification, and Category that would be great. Hi, It totally depends upon your business requirments. If you think some field is not required then just remove it or modify it to suit your requirments. There are many attributes of Account which are not even shown on Account Form by default but they are there in ...

Code Comment Web Report,
I have enabled VC++ XML comments in Visual Studio 2005. However, I do not see the "Build Comment Web Pages" under the Tools file menu (which my book says should be there--but the book was written for VS02). Did they get rid of Build Comment Web Pages in VS05, or simply move it? > I have enabled VC++ XML comments in Visual Studio 2005. However, I do not > see the "Build Comment Web Pages" under the Tools file menu (which my book > says should be there--but the book was written for VS02). Did they get rid > of Build Comment Web Pages in VS05, or simply ...

output to
I created a button to save a report in a designated folder. The date parameter is a box on the form the query for the report runs off of. I want to include that date field in my report so I know what parameters I ran the report on by looking at the contents of the folder. I tried to code the start date into my stOuputFile portion but didn't do it correctly. Here's my code so far for the button. Let me know if anything should be cleaned up as well. Thanks. Private Sub Cmd_MailEngineerRpt_Click() On Error GoTo Err_Cmd_MailEngineerRpt_Click Dim stDocName As String stD...

VB code
Hi all, I am trying to create a macro using VB. Here is what I want my macro to do : I have file1 and file 2 saved in each monthly folder like c:/month/file1.xle c:/month/file2.xle I have a spreadsheet like this A B C D E Date Unit No. Sale Amt Source1 Source2 9/4/2005 UN01 $2.00 6/8/2004 UN02 $3.00 10/5/2004 UN03 $5.00 My file1 like this colum A colum B Unit No Source1 Un02 10 Uni03 12 Now I need go to file1 to find data for Colum D(Source1) by using vlookup function based on the unit number then extrive the data to colum D and times 50%. So after I run the ma...

Design for future merging
I've created a small but somewhat complex relational database that uses autonumber fields in higher level tables as primary keys to link with foreign keys in lower level tables. At this point I have up to 5 levels of tables. I'd like to copy this database for use at 4 other remote sites, with the ultimate intention of merging all 5 back together after a few months of data entry. I've read quite a bit about how to merge databases that weren't originally designed with future merges in mind, and this ends up being quite complex with so many levels and autonumber-dependent t...

Using AutoSum Button results in miscalculations
I have a workbook that has a couple hundred worksheets in it and one o the worksheets is pulling values (via formulas) to it from many of th other sheets. Now, all the values have apparently pulled ove correctly, but when I run an AutoSum totalling all of the values i adds them incorrectly. I verified this by using a calculator and b retyping all the numbers in the next column in the worksheet and usin AutoSum to total those numbers. What could cause this? Is there a rounding problem somewhere? I hav double and triple checked my formulas and they are all correct. I jus don't understa...

Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...

Formula For Monthly Date
Hello, I have a worksheet that has products listed that are on sale at different times of the year. My question is what formula to use to get all Jan products,codes,prices etc on to new worksheet, Feb on another worksheet etc. Thanks Mare Use Autofilter. See: http://www.contextures.com/xlautofilter01.html -- Gary's Student "Mare" wrote: > Hello, > I have a worksheet that has products listed that are on sale at different > times of the year. My question is what formula to use to get all Jan > products,codes,prices etc on to new worksheet, Feb on another works...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Fixed Assets Import (6 months ago)
Hello: It really is a moot point. The situation is over, my clients are happy, and I am able to import fixed assets just fine. But, six months ago and right before service pack 1 for GP 9.0 was released, for the first time ever I was unable to import fixed assets. The FA Import Tool would show--on the "surface"--that all of my assets imported. But, the fact of the matter was that the assets did NOT import. And, there were no error messages givens to why! I had opened up a case with Microsoft, at that time. They admitted that they could not solve the mystery. And, they s...

Does any have any sample code for Store Manager Addins
I'd like to jump start some development efforts and would like to see some examples of how ADDINs have been used. Anything that hits the PurchaseOrder Table would be perfect.... My understanding is that samples are availble to certified partners at the PartnerSource web site: https://mbs.microsoft.com/partnersource/products/rms/documentation/installationsetupguides It is not clear to me if customers have direct access to it or not. "Espo" wrote: > I'd like to jump start some development efforts and would like to see some > examples of how ADDINs have been used...

On error displayes default error code instead of the specifyed one
Hi, I have added error handling code to my combo box. Private Sub searchName_AfterUpdate() ' Find the record that matches the control. Dim rs As Object On Error GoTo ERR_Handler Set rs = Me.Recordset.Clone rs.FindFirst "[sdutentId] = " & Str(Nz(Me![searchName], 0)) On Error GoTo 0 Exit Sub ERR_Handler: MsgBox "Please empty search box before continuing!" End Sub But it doesn't display the message in msgbox, it displayed default ms access error code. which is "The text you have entered isn't an item in the ...

Escape codes embedded in XML
Hello, I have built an XMLDocument object instance and I get the following string when I examine the InnerXml property: <?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1 QID=\"55111\"><Tag2 AID=\"5511101\"></Tag2></Tag1><Tag1 QID=\"55112\"><Tag2 AID=\"5511217\"></Tag2></Tag1><Tag1 QID=\"5512282\"><Tag2 AID=\"551228206\"></Tag2></Tag1><Tag1 QID=\"55114\"><Tag2 AID=\"5511406\"></Ta...

AutoPublish code
Hi, I'm looking for an autopublish product for a customer of mine. I know of one, but I'm trying to create a list of options for my customer, so if you know of one, can you please reply to this post with details. -- Regards, Ben. I've seen a couple folks develop macros to publish all projects. Not sure if that's what you're looking for.... - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, I'm looking for an autopublish product for a customer of mine. I > know of one, but I'm trying to create a list of options for m...

code to change a form's allows?
I am wondering what the code syntax is to tweak a form's allows? Allows deletions? Allows Adds? etc I know how to do it in the right-clicked form properties manually, but I need it to be based on conditions. Reason I need this is because I have one form that all users open. Depending on their window's username, I have a security table that tells me what role I want them to have in the database. Depending on their role, some users have more rights than others on this form. I want to adjust the form to allow additions for some users, but not others. Me.AllowAdditions = Fals...

Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql server) and it paste the data on 4 sheets in excel. Occasionally the reports are too big for excel. I'd like to develop a similar database that saves the results of the 4 stored procs as tables in a new Access database. right now I have it returning the results as a query. How do I save the results in another database? Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 Hi Billy The following SQL statement should do it for you: SELECT * INTO [TableName] I...

Modify Appointment Status Code attribute
Hello, Does anyone know how I can modify the pick list values that is for the Status Code attribute in the Appointment Entity. This is the pick list that appears when a user clicks on Actions->Close Appointment. All I can use is Completed or Canceled. However, I would like to add more to the list. When I open the attribute, everything is greyed out and I cannot modify this. Please advise. Thanks Hi Daryl this field is locked because it is a system attribute, and i do not know of any supported ways to customize/change values in this attribute. Regards Leon Agerlin "daryl&...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...

Hide formula, show result
I have a formula typed into a cell. The cell displays the formula not the result of the formula. The options are set to hide formulas. I know this has something to do with cell formats but I don't recall the solution. Please refresh my memory!!! Thanks Could it be that you have: tools|Options|View Tab|Formulas checked? Or do you have that formula cell formatted as Text? DWJ wrote: > > I have a formula typed into a cell. The cell displays > the formula not the result of the formula. > The options are set to hide formulas. > I know this has something to do with cel...

Credit Card Approval Code
I am using IcVerify 3.1.6 to process credit card transactions and it is working fine. But, the approval code printed on the receipt shows bunch of numbers and letters (usually two lines long), instead of 6 digit approval code. When I test the transaction using IcVerify program (not RMS software with multi-user enviorment), the approval code shows only 6 digits. Is there any way to correct this problem? Please advise. Thank you. Eric, Turn on the short answer file. In your ICV multi-user screen, type in a /O (that's an O not a zero) on the initialization string line. -- * &quo...

Rotate Label Code
Is it possible to get the details (with the rotated label control) to display the results left to right rather than top to bottom? I have the following now: If Me.Left < (2 * 1440) Then Me![Label0].Visible = True Me![Label1].Visible = True Me![Label2].Visible = True Me![CheckNo].Visible = False Me![CustNo].Visible = False Me![PostingAmt].Visible = False Me.NextRecord = False Else Me![Label0].Visible = False Me![Label1].Visible = False Me![Label2].Visible = False Me![CheckNo].Visible = True Me![CustNo].Visible = True Me![PostingAmt].Visible = True End...

How to query multiple zip codes
I have a database of over 380k records that I need to separate out by zip codes. What I am doing is getting a 50 mile radius of my directors across the country and this program comes up with a list of zip codes in that radius. Just for OR, I have 7 pages of zip codes (182 zips). Is there an easy way in Access to get only those zips? Some I can use the operator between "97005-97024", but I can't do the entire list that way. I've always wanted to figure this out. Thanks for any help to make this easier for me. I have about 48 regions to do. Hi Jerry: I do have ...

If the result of a formula is negative make equal to zero
I do have a worksheet that calculates sales profits or losses quarterly in 3 different locations. For each quarter I do have a formula that gives me the increase or decrease amounts during the last couple of years. For the total result I need a formula that converts or ignores the negative results. Any calculation result with a minus sign in the formula should be equal to zero, so it's not included in the final quaterly total. Could somebody help me with it? Thanks! =IF(yourformula<0,0,yourformula) Regards Trevor "Nrippe" <Nrippe@discussions.microsoft.com> wro...

How to write a code in VBA?
I wanted to write a VBA macro to sort sheets in alphabet order (thx Chip Pearson!), but I realized that I don't know how to do it in VBA. I'm pathetic, I know, but can someone help, please. I don't know where to write it, or how to make it work. Ok, I get it now. I didn't realize I had to run a macro after I write it. After I did that, the sheets sorted in alphabet order. Told U - pathetic. :o) "Eternity" <zabo@ravi.com> wrote in message news:bu8c1g$6oi$1@ls219.htnet.hr... > I wanted to write a VBA macro to sort sheets in alphabet order (thx Chip &...