show warning when a calculated total<0

How can I detect the condition that occurs when a calculated field becones 
negative? I would like to issue a warning message at that time.

Thanks,


0
Utf
2/10/2010 7:35:03 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
469 Views

Similar Articles

[PageSpeed] 16

Goldar,
    That's enough information to give a specific solution...
    Are you talking about a form, or a query, or a report?
    What's the calculation?
    Describe the value/s that constitute the calculation.
    Please... more info...

    Essentially though... what event/s would effect/change that calculation 
value?
    Use those events to trigger an evaluation of the new value, and respond 
accordingly.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."


"Goldar" <Goldar@discussions.microsoft.com> wrote in message 
news:A1B059A9-6CB1-4318-BE9F-EB3E67E3CCD2@microsoft.com...
> How can I detect the condition that occurs when a calculated field becones
> negative? I would like to issue a warning message at that time.
>
> Thanks,
>
> 


0
Al
2/10/2010 8:01:42 PM
Goldar wrote:

>How can I detect the condition that occurs when a calculated field becones 
>negative? I would like to issue a warning message at that time.


You would have to do the calculation using VBA code in an
event procedure instead of a text box expression.

OTOH, you could scrap the warning message idea and change
the textbox's ForeColor by using a custom format for the
text box.  E.g.
	0.00;[RED]-0.00


-- 
Marsh
MVP [MS Access]
0
Marshall
2/10/2010 8:28:44 PM
It's a summation on a very simple form. The form contains up to 5 lines of 
data where each line contains: [un[Amount approved], [Amount spent], and a 
line total =nz([Amount approved],0) - nz([Amount spent],0)). On the sixth 
line are column totals and a total of all the line totals is 
=nz([LineTotal1],0)+nz([LineTotal2],0)+nz([LineTotal3],0)+nz([LineTotal4],0)+nz([LineTotal5],0). 
What I would like to have happen would be that when the final total (line 6) 
is calculated to be negative, I could issue a warning to the operator. 
Generally, this condition indicated an input error.

Thanks,


"Al Campagna" wrote:

> Goldar,
>     That's enough information to give a specific solution...
>     Are you talking about a form, or a query, or a report?
>     What's the calculation?
>     Describe the value/s that constitute the calculation.
>     Please... more info...
> 
>     Essentially though... what event/s would effect/change that calculation 
> value?
>     Use those events to trigger an evaluation of the new value, and respond 
> accordingly.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2007-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> 
> "Goldar" <Goldar@discussions.microsoft.com> wrote in message 
> news:A1B059A9-6CB1-4318-BE9F-EB3E67E3CCD2@microsoft.com...
> > How can I detect the condition that occurs when a calculated field becones
> > negative? I would like to issue a warning message at that time.
> >
> > Thanks,
> >
> > 
> 
> 
> .
> 
0
Utf
2/10/2010 9:09:01 PM
Goldar,
    If a user message is necessary, there are many ways to handle that.
(ex. a Sub or a Function, etc...)

You can use the AfterUpdate event of LT1 through LT5 to check the
calculated value and post a message accordingly.
(you didn't indicate the name if the control that adds up all 5 LineTotals, 
so
I'll use [GTotal])

Private Sub LineTotal1_AfterUpdate()
    If GTotal <= 0 Then
        Beep
        MsgBox "GTotal is less than 0", vbOKOnly
    End if
End Sub

-OR------------

Private Function CheckTotals()
    If GTotal <= 0 Then
        Beep
        MsgBox "GTotal is less than 0", vbOKOnly
    End if
End Function
    Then place...
        =CheckTotals()
in the AfterUpdate property textbox of each LineTotal1 through LineTotal5
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."


"Goldar" <Goldar@discussions.microsoft.com> wrote in message 
news:8060E3EE-90B6-49D6-A9F5-CFFEFF96808C@microsoft.com...
> It's a summation on a very simple form. The form contains up to 5 lines of
> data where each line contains: [un[Amount approved], [Amount spent], and a
> line total =nz([Amount approved],0) - nz([Amount spent],0)). On the sixth
> line are column totals and a total of all the line totals is
> =nz([LineTotal1],0)+nz([LineTotal2],0)+nz([LineTotal3],0)+nz([LineTotal4],0)+nz([LineTotal5],0).
> What I would like to have happen would be that when the final total (line 
> 6)
> is calculated to be negative, I could issue a warning to the operator.
> Generally, this condition indicated an input error.
>
> Thanks,
>
>
> "Al Campagna" wrote:
>
>> Goldar,
>>     That's enough information to give a specific solution...
>>     Are you talking about a form, or a query, or a report?
>>     What's the calculation?
>>     Describe the value/s that constitute the calculation.
>>     Please... more info...
>>
>>     Essentially though... what event/s would effect/change that 
>> calculation
>> value?
>>     Use those events to trigger an evaluation of the new value, and 
>> respond
>> accordingly.
>> -- 
>>     hth
>>     Al Campagna
>>     Microsoft Access MVP 2007-2009
>>     http://home.comcast.net/~cccsolutions/index.html
>>
>>     "Find a job that you love... and you'll never work a day in your 
>> life."
>>
>>
>> "Goldar" <Goldar@discussions.microsoft.com> wrote in message
>> news:A1B059A9-6CB1-4318-BE9F-EB3E67E3CCD2@microsoft.com...
>> > How can I detect the condition that occurs when a calculated field 
>> > becones
>> > negative? I would like to issue a warning message at that time.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>> .
>> 


0
Al
2/10/2010 10:10:20 PM
Reply:

Similar Artilces:

Can't get a 0 to show on the x-axis!
I am trying to chart a single line graph to show the variation in decibels over a period of 600 seconds. I am unable to get the category axis to show a 0 at the origin on the x-axis - the best I can do is to show it at 1, 51, 101 etc. If I try to change the value to 0 by formatting the axis I am told that the value 0 is invalid. Can anyone tell me how to get the x-axis scale starting from 0? Many thanks, Noel I am using Excel 2002 (SP3) Sorry, I have just discovered that I need to use an XY Scatter graph and not a Line one! Noel "Noel S Pamfree" <Noel.spamfree@hot...

Reinstalling CRM 4.0 but preserving data
We are having terrible trouble having upgraded from CRM 3.0 to 4.0. We are trying to use the Workflows to manage tasks etc., but without fail the Workflow goes into a "Waiting for Resources" state and never does anything. We've looked at all the obvious candidates (the Async Service, for example) and have tried the various fixes (more information here; http://www.themssforum.com/Crm/Workflows-Waiting/) We're now at the stage of considering reinstalling CRM 4.0. However, we have migrated a large amount of data from our CRM 3.0 installation. Can we safely backup the existi...

OMNI Tools with GP9.0
I've read an article that says I cannot use OMNI Tools with GP9.0 anymore. Can someone help me with this? I've purchased OMNI for login validations. I'm using SQL2000 I originally developed Omni Tools which is now sold by http://www.rocktonsoftware.com. I would be interested to see this article as it is not correct. Omni Password will still work with all versions of Great Plains/Dynamics GP and all database versions. You can even have it working along side the functionality offered by Dynamics GP 9.0 with SQL Server 2005 and Windows Server 2003 and Active Directory. Thi...

multiple IF calculations
I've figured out how to make cell X display the number of hours entered into D9 if the text "Stat Worked" appears in D11, but now I need to do the same for columns E through Q, and sum the results. =IF(D11="Stat worked", D9, 0) Any thoughts? mel wrote: > I've figured out how to make cell X display the number of hours entered into > D9 if the text "Stat Worked" appears in D11, but now I need to do the same > for columns E through Q, and sum the results. > > =IF(D11="Stat worked", D9, 0) > > Any thoug...

New workstation, AMD Athlon 64, OK for GP 8.0?
We are getting ready to buy a new workstation and I want to make sure it is ok with Great Plains V8.0. Is the 64bit compatiblity problems with the OS or the processor? It seems like the majority of the workstations out there are now 64bit. I am looking at a Dell Dimension E521 with an AMD Athlon 64 3200+ processor and XP Pro. We will be upgrading to 9.0 in the near future, but I don't have plans to do this before buying this new workstation. ...

Unable to uninstall CRM 3.0 client from any other user, including Admin (Local or Domain)
How do I uninstall CRM 3.0 client that was installed on another user account we don't have access to? The only way we can uninstall the client is if we log in as the user who used it. When people switch computers and a new user needs that computer, the only way to enable them to use CRM outlook client is to uninstall it on the old user and reinstall on the new. Why!? Any help is appreciated. ...

RMS 2.0 #3
So if RMS 2.0 if ready for Shipping mid Jan, two questions . . . 1) When am I likely to get it in the UK? 2) What fantastic new features can I expect? Chris This is a multi-part message in MIME format. ------=_NextPart_000_0356_01C72049.45681920 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Chris, 1) How slowly do the boats take to get to the UK? 2) Fantastic? Umm. Useful, a couple/few. For whatever reason, they = don't want us to mention anything publicly. If you subscribe to the = Insights newsletter, I would probably...

Outlook 2007 shows no pictures in HTML mail
Only red crosses. Trustcenter is disabled but still no pictures. Clicking 'Download pictures now' doesn't work neither. -new profile -deleting cashes -repair install no effect. ...

In Excel 2002 highlighted cells show when printed, not on screen though.
I cannot get Excel 2002 to show patterns or colors on the screen. They print correctly on the printer. I have checked options, and see nothing that seems relevant...HELP! OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/default.aspx?scid=kb;en-us;320531 Read this KB -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Borcherding" <slborcherding@juno.com> wrote in message news:592201c376f8$572f4af0$a601280a@phx.gbl... > I cannot get Excel 2002 to show patterns or colors on the > screen. They print...

Office 2003
Since a few days ago, I have started to receive the warning shown below every time I start Outlook. I have no idea where this comes from or how to resolve it Help Thanks "This file contains macros with an expired or revoked signature. Since you are running under high security level thse macros will be disabled" I found the answer. It is the macro security setting in MS Office. What I don't know is how it got changed from medium to high "Jim McColl" <jamccoll@sympatico.ca> wrote in message news:B749422D-D6DE-4938-AAB4-5FABE8A3C61E@microsoft.com... &g...

Strange calculation
I need to populate a control on a form with the product of two other values on the same form. I use a macro with a SetValue statement to do this, but am getting a strange result on the calculation. Rent = Cost*LeaseRate Cost = $15615 LeaseRate = .0278 When I run the Setvalue macro, it results in Rent = $434.090 (I used the Number, Decimal data type for all three numerical values). Pull out a calculator. $15615*.0278 = 434.097 What????? Access is getting $434.090, correct value is $434.097 which would round to $434.10. This isn't just formatting or rounding, what's going on? I...

WARNING: Altering DBs in Access 2010 may make them not run under 2
I have multiple computers, and thought I would try the beta of Office 2010 on 2 of them, so uninstalled 2007 and installed 2010. On one of these machines I made several changes to an Access DB. I then tried to open it on a machine with Access 2007 and received an error message that it could not open the file, that it was likely corrupt. It still opens fine on the beta machine, as well as another machine with 2010 beta on it. It may be related to 2010 eliminating MSCAL.OCX. I had to clear the "missing" reference in references in VB as it is no longer supported (MS ...

#DIV/0! error 12-06-09
I'm using the following formula =100%-(H6/J4) to get a percentage, however result is #DIV/0! error Can anybody help with this error? Thanks for your help If J4 is zero or blank, you'll get this error. Check to make sure there is a value in that cell. "Memo Larach" wrote: > I'm using the following formula =100%-(H6/J4) to get a percentage, > however result is #DIV/0! error > > Can anybody help with this error? > > > Thanks for your help Sorry, I forgot to add that if you still get the error even i...

Autofilter shows no results, but there should be because there are matches
the column that I am trying to use autofilter on has nothing but numbers in it. But when I do a custom filter for a number that begins with '5' (for example), it shows no results. Even though there are plenty of numbers that start with '5'. I do the same excercise on another worksheet that is formatted the same, and it works fine. If I go back to the non-working worksheet and retype the number in each cell the autofilter will work. WHY OH WHY is this so? That happened to me whenever I imported data from a particular database. On import, somehow a little 1/2 space was put i...

Set track = "All e-mail messages" in DB or bulk CRM4.0
I have a client who wants to change all users or a sub-set of users to track all e-mail messages, but they don't want to have to go to each machine to do it or log on as each user. Is there any SUPPORTED way to change it in bulk (like a Group Policy type solution)? I notice that changing the setting in the "Set Personal Options" window updates two fields in the UserSettingsBase table: UserSettingsBase.IgnoreUnsolicitedEmail=0 UserSettingsBase.IncomingEmailFilteringMethod=0 I assume that changing these in SQL would be unsupported (and may cause other problems). I also assume...

New messages not showing in Inbox
Hi, We use Outlook 2002 SP2. On a couple of desktops we do not see new messages showing up unless we open an existing message. Once we close the existing message, all new items are there. When we try to click the Send/Receive icon, it does not bring the new items. The Outlook client seem to contact the Exchange server only when we open an existing item. Any thoughts? Thank you. P. Could it be the 'views'? Did you change anything in the 'views'? Try searching for 'Views' in the help file... Good luck! "P" <plavallee@rcn.......com> wrote in messa...

Antigen 8.0 to 9.0 upgrade problem
I have recently upgraded Antigen version 8 to Antigen version 9. Version 8 had installed a folder into each user's mailbox folder named ASM Junk Mail folder. With the upgrade ASM Junk Mail folder is no longer needed. How to I go about removing the folder from all the user's mailbox. Is there a way to systematically remove this empty folder? and making sure it doesn't reappear? Thanks ...

M'03 not showing 2004 entries
I am creating transactions that are dated 2004. They show in the reports... but not in the account register (checking in this case) My balance has gone totaly whack... off by thousands. anybody else gettingthis with Money 2003? Something very similiar is happening to me. All my scheduled bills are showing up in the register, BUT my scheduled deposits are not. This of course really screws up the cash flow. This is only for transactions scheduled for 2004. What am I missing? There are no end dates to the scheduled deposits. >-----Original Message----- >I am creating tra...

Updating GP 10.0 to SP2 genericPath error
I am trying to update a GP10.0 installation to the latest and greatest. When I launch the MicrosoftDynamicsGP-KB952789-v10-ENU.msp package, it starts working, then comes up with this error: Specified argument was out of the range if valid values. Parameter name: genericPath. When I click "OK" the install terminates. Any ideas? I am doing a test install and had some problems so I am starting over. When trying to unistall, I am getting the same error message. I have v10 sp3? KB955679. It won't let me uninstall or do a repair install. "Wes VanAlstyne" wrote: >...

Cannot make configuration changes in CRM 4.0 Outlook client
I've recently updated to CRM 4.0 from 3.0. After this upgrade, I am unable to make configuration changes to my outlook client. I go to CRM->Options and make the necessary changes, but when I click OK it just hangs and eventually gives me a microsoft reporting pop-up message. Any ideas? Thanks Do the users have enough rights? I found that if users did not have enought rights the outlook client would work, but then would not let me change options. Try adding a user the the System Administor role and trying again. If it then works it means they are missing a right they need. Un...

Win CE 6.0 R3
Experts, How to implement Single SSID feature to my existing, working, USB WiFi connection. Is it the registry that need to be dealth with or is it the WZC app that needs to be modified ? Also dont want any popup WZC when system starts. It should directly connect to the SSID we pre-designate using Single SSID. Any tips will help regards, GS-ICN I don't know what "Single SSID" is. You could write a program that runs on startup to check the preferred list of SSIDs associated with your wireless adapter and, if there are none, add one that you describe...

Print preview shows blank sheet
I am new at this but whenever I use print preview my work always comes up but now for some reason all I get is a blank page and nothing prints out . Possibly you have a print area set on a blank cell or cells. To clear (XL2003): File, Print Area, Clear Print Area OR to set a range to print: Highlight the range you want printed: File, Print Area, Set Print Area XL 2007: http://office.microsoft.com/en-au/excel/HP100215421033.aspx -- Steve "gillygalloo" <gillygalloo@discussions.microsoft.com> wrote in message news:76E02D70-4D0A-4421-8B09-A4EF93642BFD@microsoft.com......

Inbound email activites show up in Assigned or In-progress as completed and can't be removed
I have an issue with the International English version of CRM (1.2). In my Workplace new inbound email shows up in my Assigned folder. I can then re-assign or accept, but as the email is completed it can't be removed. It is the same if I accept the email. It is moved to my In-Progress folder, but I do not have the ability to move it from my queue. I though this was by design and when the regarding Case was closed these activities would be removed then. However they still remain. Has anyone seen this behaviour or it is really simple and I have just missed it? Greg If i understand you c...

Calculations #4
Hi, when I am calculating my totals, my sum is more than what is listed. How do I make the necessary changes to reflect the correct sum. Thanks, Jessica This might describe your problem: http://www.mcgimpsey.com/excel/pennyoff.html In article <1959a01c44d55$091c3920$a601280a@phx.gbl>, "Jessica" <anonymous@discussions.microsoft.com> wrote: > Hi, > > when I am calculating my totals, my sum is more than what > is listed. How do I make the necessary changes to reflect > the correct sum. > > Thanks, > > Jessica Thanks for such ...

Opening .xls file "outside" Excel not showing
Hi all. This is kind of weard, but here it goes: when I open workbook from Excel (file -> open) it works just fine, but... When I'm trying to open the same workbook via "link", for example double clicking the file from windows explorer -> Excel opens, but the file is not visible. And when I choose: view -> full screen -> it comes visible. Do you have any idea what is wrong? System is Win XP pro and Office XP. This happens to all .xls files... TIA Regards, Alfred Hi Alfred, Try the following steps: 1. Exit Excel 2. From Start, Run. Enter the command: "excel...