Re: calculated field

Tech-Archive recommends: Fix windows errors by optimizing your registry

Switch(TestCondition,ValueToReturn,TestCondition,ValueToReturn, ...)

It steps through until the first TestCondition returns True and then returns the
associated ValueToReturn.

Try Opening up VBA and looking in that help as Switch is a VBA function.

neeraj wrote:
>
> It worked, thanks, whats the syntax of the switch field. I couldn't find ot
> in access help
>
> "John Spencer (MVP)" wrote:
>
> > You can use a nested IIF statement or a SWITCH statement
> >
> > NEWField: SWITCH(Field1=val1 and Field2=val2 and Field3=val3, Val1-val2,
> > Field1=val1 and Field2=val2 and Field3=val4, val1-val2/100,
> > Field1=val1 and Field2=val2 and Field3=val5, val5*val2)
> >
> > WIth your example, you could combine IIF and the Switch function to
> >
> > NewField: IIF(Field1=val1 and Field2=val2,
> > SWITCH(Field3=val3, Val1-val2,
> > Field3=val4, val1-val2/100,
> > Field3=val5, val5*val2),Null)
> >
> > You didn't say what you wanted if none of the criteria matched, so I assumed Null
> >
> > neeraj wrote:
> > >
> > > I am creating a new field say, NewField in a query which uses other fields.
> > > I want the logic to be like this:
> > > If Field1=val1 and Field2=val2 and Field3=val3 then NewField = val1-val2
> > > If Field1=val1 and Field2=val2 and Field3=val4 then New Field = val1-val2/100
> > > If Field1=val1 and Field2=val2 and Field3=val5 then New Field = val5*val2
> > > Basically, val1 and val2 dont change and Field 3 is like a case statement
> > > and depending upon the case value, the operation for calculation of NewField
> > > changes. How can I create this NewField in Access
> >
.

Relevant Pages

  • Re: calculated field
    ... It worked, thanks, whats the syntax of the switch field. ... > You can use a nested IIF statement or a SWITCH statement ... >> and depending upon the case value, the operation for calculation of NewField ...
    (microsoft.public.access.queries)
  • Re: Complex? reporting problem
    ... As much as I love VBA, I don't think that you need it here. ... your day of year conversion to your "switch value." ... I presume that your switch value is relative to the month where the ... Revising code can ...
    (comp.databases.ms-access)
  • Re: Problem when another app activated from macro
    ... > control to it and several other controls, ... > However if you switch to some another application manually ... could try "show" on the form (in VBA). ... AppleScript: The Definitive Guide ...
    (microsoft.public.mac.office.word)
  • Re: VB6 vs VB .Net
    ... I'm gradually going to convert all my VB and VBA into VB .NET. ... > VB6 has a limited future potential, ... > generate those kinds of articles THREE YEARS AGO. ... > it doesn't any more, switch to anything else, *anything at all* other than ...
    (microsoft.public.vb.general.discussion)
  • Re: calculated field
    ... Add one pair to the switch statement. ... NewField: IIF(Field1=val1 and Field2=val2, ...
    (microsoft.public.access.queries)