Re: [AccessDevelopers] Re: Format

[A.D.Tejpal]

A.T.,       You wish to enforce variable decimal positions as per employee type. This implies a dynamic format string, length of which would be determined by EmpType (Employee Type).       It is suggested that you set up an auxiliary table named T_EmpType having two fields named EmpType (primary key) and DecCount (number type). DecCount shows the number of decimal positions needed for the given EmpType. Populate this table with four records, pertaining to the four employee types.       Sample query given below, using String() function for dynamic formatting, should get you the desired results. T_EmpPay is the name of your main table. In this table, EmpType is the foreign key. The tables are joined on EmpType.   Best wishes, A.D.Tejpal   ==================================== SELECT T_EmpPay.*, IIf([GrossBilling]>0,Format([ComNo]*[ComPctAmt],"#." & String([DecCount],"0")),0) AS ComPct FROM T_EmpPay INNER JOIN T_EmpType ON T_EmpPay.EmpType = T_EmpType.EmpType; ====================================   ----- Original Message ----- From: jmw95823 To: AccessDevelopers@yahoogroups.com Sent: Monday, August 08, 2005 23:01 Subject: [AccessDevelopers] Re: Format I would try one at a time and see what results you get.  I think the $$format would change for each condition.  I don't know whether the Switch function or another similar to immediate If (IIF) might be better.  Maybe someone else will chime in to further educate.  Jim --- In AccessDevelopers@yahoogroups.com, "torres0157" <[EMAIL PROTECTED]...> wrote: > Yes, > I have for a total of 4 formats to display on the form. > How many IIf statements can I use. > > > --- In AccessDevelopers@yahoogroups.com, "jmw95823" <[EMAIL PROTECTED]...> > wrote: > > Could you use another IIf function designating employee type > > conditions and then wrap that around your exisiting IIf function?  > Jim > > --- In AccessDevelopers@yahoogroups.com, "torres0157" > <[EMAIL PROTECTED]...> > > wrote: > > > Good Morning, > > >  I have a calculated field ComPct on a Form created in a query. > > >   ComPct: IIf([GrossBilling]>0,Format([ComNo]* > > [ComPctAmt],"#.###"),0). > > >  This Calculated (ComPct) can have various Decimal Places. > Depending > > >  on the Employee type. I there a function in access that I can > > >  use to increase the decimal Positions to the results field > depending > > >  depending on the value of another field. > > >  > > >  Thanks         A.T. Please zip all files prior to uploading to Files section. SPONSORED LINKS Microsoft access database Microsoft access developer Microsoft access Microsoft access database design Microsoft access database training YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.