Users request eliminating 0 in field default on form and IsError returns #Num

I have 2 irritating issues, and I'm hoping someone can help. I haven't
found a solution in Help or searching posts.

1. Field displays #Num or #Div/0 on new record.  Calcs correctly after
data is entered.
The controlsource of the field is below.  I thought the IsError would
eliminate the #num or #div/0??

=IIf(IsError(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100,"",
(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)


2.  The users end up typing 600 when they mean to type 60.  Is there
any way to get rid of that default 0?
I have 2 fields on this form that are impacted:

Pack
Quantity

Table default is 0; form default is null
But when you tab into the field or click in it, you see 0.  The users
end up typing 600 when they mean to type 60.  Is there any way to get
rid of that 0 showing up?

Thanks
sara

0
saraqpost
5/10/2007 6:10:42 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1133 Views

Similar Articles

[PageSpeed] 19

<saraqpost@yahoo.com> wrote in message 
news:1178820641.956080.92230@y5g2000hsa.googlegroups.com...
>I have 2 irritating issues, and I'm hoping someone can help. I haven't
> found a solution in Help or searching posts.
>
> 1. Field displays #Num or #Div/0 on new record.  Calcs correctly after
> data is entered.
> The controlsource of the field is below.  I thought the IsError would
> eliminate the #num or #div/0??
>
> =IIf(IsError(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100,"",
> (([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)
>
>
> 2.  The users end up typing 600 when they mean to type 60.  Is there
> any way to get rid of that default 0?
> I have 2 fields on this form that are impacted:
>
> Pack
> Quantity
>
> Table default is 0; form default is null
> But when you tab into the field or click in it, you see 0.  The users
> end up typing 600 when they mean to type 60.  Is there any way to get
> rid of that 0 showing up?
>
> Thanks
> sara
>

1. I don't think that's how IsError works. You probably should just check 
[txtTotalRetail] to see if it's not zero. You could do it this way:

=IIf(([txtTotalRetail]=0),"",(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)

2. The form is still picking up the default value from the table. You'll 
need to remove the default in the table as well.

Carl Rapson


0
Carl
5/10/2007 9:50:24 PM
On May 10, 5:50 pm, "Carl Rapson" <mr.mxyzp...@newsgroups.nospam>
wrote:
> <saraqp...@yahoo.com> wrote in message
>
> news:1178820641.956080.92230@y5g2000hsa.googlegroups.com...
>
>
>
>
>
> >I have 2 irritating issues, and I'm hoping someone can help. I haven't
> > found a solution in Help or searching posts.
>
> > 1. Field displays #Num or #Div/0 on new record.  Calcs correctly after
> > data is entered.
> > The controlsource of the field is below.  I thought the IsError would
> > eliminate the #num or #div/0??
>
> > =3DIIf(IsError(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100,"",
> > (([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)
>
> > 2.  The users end up typing 600 when they mean to type 60.  Is there
> > any way to get rid of that default 0?
> > I have 2 fields on this form that are impacted:
>
> > Pack
> > Quantity
>
> > Table default is 0; form default is null
> > But when you tab into the field or click in it, you see 0.  The users
> > end up typing 600 when they mean to type 60.  Is there any way to get
> > rid of that 0 showing up?
>
> > Thanks
> > sara
>
> 1. I don't think that's how IsError works. You probably should just check
> [txtTotalRetail] to see if it's not zero. You could do it this way:
>
> =3DIIf(([txtTotalRetail]=3D0),"",(([txtTotRetail]-[txtTotCost])/[txtTotRe=
tail])=AD*100)
>
> 2. The form is still picking up the default value from the table. You'll
> need to remove the default in the table as well.
>
> Carl Rapson- Hide quoted text -
>
> - Show quoted text -

Thanks, Carl.

I got the first to work.  The funny thing is, I'd used that *solution*
in a report and don't know why I didn't think to apply the same to the
form.  Hopefully next time!  Anyway, it works now.

On the second - is there any danger in removing the default value on
the table?  These are all numeric fields, and some are optional.  Am I
setting myself up for problems in the future if the default is gone?
Will the fields on a new record still default to 0 or will they be
Null?

I'm worried that if they're Null, I'll have to nz all over the
place....Can be done, but may be more work (I am the sole "programmer"
here) than the benefit to the user.  I'm still quite new at this, but
I have learned to think ahead - don't do something without considering
its impact (where possible) on the future.

Your thoughts?  (And anyone else, too?)
Sara
thanks

0
saraqpost
5/11/2007 2:48:37 PM
<saraqpost@yahoo.com> wrote in message 
news:1178894917.484377.3950@y80g2000hsf.googlegroups.com...
On May 10, 5:50 pm, "Carl Rapson" <mr.mxyzp...@newsgroups.nospam>
wrote:
> <saraqp...@yahoo.com> wrote in message
>
> news:1178820641.956080.92230@y5g2000hsa.googlegroups.com...
>
>
>
>
>
> >I have 2 irritating issues, and I'm hoping someone can help. I haven't
> > found a solution in Help or searching posts.
>
> > 1. Field displays #Num or #Div/0 on new record.  Calcs correctly after
> > data is entered.
> > The controlsource of the field is below.  I thought the IsError would
> > eliminate the #num or #div/0??
>
> > =IIf(IsError(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100,"",
> > (([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)
>
> > 2.  The users end up typing 600 when they mean to type 60.  Is there
> > any way to get rid of that default 0?
> > I have 2 fields on this form that are impacted:
>
> > Pack
> > Quantity
>
> > Table default is 0; form default is null
> > But when you tab into the field or click in it, you see 0.  The users
> > end up typing 600 when they mean to type 60.  Is there any way to get
> > rid of that 0 showing up?
>
> > Thanks
> > sara
>
> 1. I don't think that's how IsError works. You probably should just check
> [txtTotalRetail] to see if it's not zero. You could do it this way:
>
> =IIf(([txtTotalRetail]=0),"",(([txtTotRetail]-[txtTotCost])/[txtTotRetail])�*100)
>
> 2. The form is still picking up the default value from the table. You'll
> need to remove the default in the table as well.
>
> Carl Rapson- Hide quoted text -
>
> - Show quoted text -

Thanks, Carl.

I got the first to work.  The funny thing is, I'd used that *solution*
in a report and don't know why I didn't think to apply the same to the
form.  Hopefully next time!  Anyway, it works now.

On the second - is there any danger in removing the default value on
the table?  These are all numeric fields, and some are optional.  Am I
setting myself up for problems in the future if the default is gone?
Will the fields on a new record still default to 0 or will they be
Null?

I'm worried that if they're Null, I'll have to nz all over the
place....Can be done, but may be more work (I am the sole "programmer"
here) than the benefit to the user.  I'm still quite new at this, but
I have learned to think ahead - don't do something without considering
its impact (where possible) on the future.

Your thoughts?  (And anyone else, too?)
Sara
thanks

Yes, the value will be Null instead of zero if you remove the default. 
You'll have to determine which is more trouble - the users accidentally 
leaving the zero in the field or you (as the programmer) having to remember 
to test for Null. I don't know of any other alternative. One thing, you 
could also test the magnitide of the number in the BeforeUpdate event of the 
control and see if it's reasonable. For example, if the number should always 
be less than 100, then 600 probably means they didn't overwrite the zero 
default.

Carl Rapson


0
Carl
5/11/2007 3:59:07 PM
On May 11, 11:59 am, "Carl Rapson" <mr.mxyzp...@newsgroups.nospam>
wrote:
> <saraqp...@yahoo.com> wrote in message
>
> news:1178894917.484377.3950@y80g2000hsf.googlegroups.com...
> On May 10, 5:50 pm, "Carl Rapson" <mr.mxyzp...@newsgroups.nospam>
> wrote:
>
>
>
>
>
> > <saraqp...@yahoo.com> wrote in message
>
> >news:1178820641.956080.92230@y5g2000hsa.googlegroups.com...
>
> > >I have 2 irritating issues, and I'm hoping someone can help. I haven't
> > > found a solution in Help or searching posts.
>
> > > 1. Field displays #Num or #Div/0 on new record.  Calcs correctly after
> > > data is entered.
> > > The controlsource of the field is below.  I thought the IsError would
> > > eliminate the #num or #div/0??
>
> > > =3DIIf(IsError(([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100,"",
> > > (([txtTotRetail]-[txtTotCost])/[txtTotRetail])*100)
>
> > > 2.  The users end up typing 600 when they mean to type 60.  Is there
> > > any way to get rid of that default 0?
> > > I have 2 fields on this form that are impacted:
>
> > > Pack
> > > Quantity
>
> > > Table default is 0; form default is null
> > > But when you tab into the field or click in it, you see 0.  The users
> > > end up typing 600 when they mean to type 60.  Is there any way to get
> > > rid of that 0 showing up?
>
> > > Thanks
> > > sara
>
> > 1. I don't think that's how IsError works. You probably should just che=
ck
> > [txtTotalRetail] to see if it's not zero. You could do it this way:
>
> > =3DIIf(([txtTotalRetail]=3D0),"",(([txtTotRetail]-[txtTotCost])/[txtTot=
Retail])=AD=AD*100)
>
> > 2. The form is still picking up the default value from the table. You'll
> > need to remove the default in the table as well.
>
> > Carl Rapson- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks, Carl.
>
> I got the first to work.  The funny thing is, I'd used that *solution*
> in a report and don't know why I didn't think to apply the same to the
> form.  Hopefully next time!  Anyway, it works now.
>
> On the second - is there any danger in removing the default value on
> the table?  These are all numeric fields, and some are optional.  Am I
> setting myself up for problems in the future if the default is gone?
> Will the fields on a new record still default to 0 or will they be
> Null?
>
> I'm worried that if they're Null, I'll have to nz all over the
> place....Can be done, but may be more work (I am the sole "programmer"
> here) than the benefit to the user.  I'm still quite new at this, but
> I have learned to think ahead - don't do something without considering
> its impact (where possible) on the future.
>
> Your thoughts?  (And anyone else, too?)
> Sara
> thanks
>
> Yes, the value will be Null instead of zero if you remove the default.
> You'll have to determine which is more trouble - the users accidentally
> leaving the zero in the field or you (as the programmer) having to rememb=
er
> to test for Null. I don't know of any other alternative. One thing, you
> could also test the magnitide of the number in the BeforeUpdate event of =
the
> control and see if it's reasonable. For example, if the number should alw=
ays
> be less than 100, then 600 probably means they didn't overwrite the zero
> default.
>
> Carl Rapson- Hide quoted text -
>
> - Show quoted text -

Hey!  I've learned something and remembered it!  I think that you
confirmed what I had guessed (feared?).  It's far better for the
company for the users to look at what they're doing (and me test for
reasonable limits), than for me to constantly have to trap for Nulls.

Thanks -
sara

0
saraqpost
5/11/2007 4:08:30 PM
Reply:

Similar Artilces:

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...

Shortcut to other user's folders
At our office we view other user's folders by: File:Open:Other user's folder... Is there a way to make a shortcut in the "Outlook Shortcuts" pane on the left to one or more of these user's folders? Thanks! Edit: This is in Outlook 2000 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ bump, nobody? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from ht...

automaticaly create a variable sized table from user input
Hi, How can I user data input to a cell by a user to create a table with that number of rows. eg. User enters 1024 in B2 165 in B3 12.5 in B4 A10 would contain 1, B10 would contain =B3+(A10*$B$4) A11 would contain 2, B11 would contain =B3+(A11*$B$4) etc to 1024 The user entered number in B2 could be any whole number between 1 and 1024 Thanks, Iain I could make this better but other things to do right now so try this. Uncomment the last line to remove the formula and just leave the values. Sub makeformula() Range("a10") = 1 Range("b10").Formula = "=B3+(A10*$B$4)...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Word2007. Checkbox. No Form
Hello! We have a document (not a form) that we would like to use checkboxes in. This document has a couple of sections where it would be useful if the user could check if something is applicable. We can't protect the whole document. Thank you for your help! Stacey I actually used this feature in a form this morning. Although you don't say what version of Word you are using, you can access the Control Toolbox toolbar in Word 2003, or in Word 2007, it is on Developer tab. To show the developer tab, click the Microsoft Office Button and then click Word Options. S...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

CRM 3.0 Login Problems
Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

Unable to sort on customer CRM 4.0
Hi When you try to change the campaign response views to have customer as sorting CRM gives an error. You can't even click on customer column to sort when it's now customer that is default sorting. Is this a bug ? same here. I think its a bug "Help needed" wrote: > Hi > > When you try to change the campaign response views to have customer as > sorting CRM gives an error. You can't even click on customer column to sort > when it's now customer that is default sorting. > > Is this a bug ? > > > > ...

how do I import my contacts from IncrediMail 2.0 to MOutlook2007
I am new to using microsoft outlook 2007 and am searching for a way to upload my contact list, from Incredimail 2.0 to Outlook 2007. Any help would be definitely appreciated, thanks! How do I save my IncrediMail Address Book and move it to a different location? http://help.incredimail.com/incredimail/help_center/help_article.aspx?is=t&article_id=67&lang_id=9 In Outlook - File --> Import and Export ... import the CSV file created via process noted above Karl -- ____________________________________________________________ Karl Timmermans - The Claxton Group Co...

automating email marketing with workflow in CRM 4.0
Hello, I am attempting to prepare a client demo that demonstrates some of the features of Microsoft CRM 4. The potential client is a staffing agency, and I have spent some time thinking about how they might use the product. Here is a business process I have thought up that, if I could get it to work, would be great to show at a demo. 1. An account calls about an opportunity. The opportunity is entered. The opportunity has certain skills attached to it, such as: SQL Server, Linux, C#, etc 2. Contacts (candidates) also have certain skills attached to them, such as: SQL Server, Linux, C...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Picture control on a form
What is the best way to fill a form with a picture control so that when the client window is resized the picture control will fill the form? -- Just Al Take a look at this and see if this is what you want: http://www.codeproject.com/dialog/bmpdlg01.asp Tom "Al" <Al@discussions.microsoft.com> wrote in message news:5BEA0FA2-3F95-4090-B908-A75DBF1D19DE@microsoft.com... > What is the best way to fill a form with a picture control so that when > the > client window is resized the picture control will fill the form? > -- > Just Al Thank You! -- Just Al &...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...