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.
