Tejpal, I like your theory but my query is using approx. 5 tables. Also the key to the Commission table in which the fields are stored has a key as projectNo. I went ahead and tried thiS but keep getting msg 'Check the subquery's syntax and enclose the subquery in parenthese'
A.T. --- In [email protected], "A.D.Tejpal" <[EMAIL PROTECTED]> wrote: > 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: [email protected] > 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 [email protected], "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 [email protected], "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 [email protected], "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. ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12hlb90o2/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123543758/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
