Re: IIF Function Question
From: Robyn H. (RobynH_at_discussions.microsoft.com)
Date: 09/23/04
- Next message: Ken Snell [MVP]: "Re: Rounding numbers"
- Previous message: Gary Walter: "Re: Formatting date query Column by week."
- In reply to: Michel Walsh: "Re: IIF Function Question"
- Next in thread: Michel Walsh: "Re: IIF Function Question"
- Reply: Michel Walsh: "Re: IIF Function Question"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Sep 2004 05:41:03 -0700
Michael, for some reason (I'm tearing my hair out!) it's still not working!
Is there ANY way I could email you my entire database? I'm just building
it, so there are only a few sample records in there, and not much else. I
guess I just have no idea where I have gone wrong?
Please let me know.
Thanks so much for your time and help!
Robyn
"Michel Walsh" wrote:
> Hi,
>
>
> Yep, there is at least one.
>
>
> [tblClientCommKeep.BattenCapKeep]
>
> should be
>
> [tblClientCommKeep].[BattenCapKeep]
>
>
> or, even better, not [ ] at all, since your names are ok (no space, no
> reserved character). As it is now, it is looking for a field or a parameter
> where the name is exactly [tblClientCommKeep.BattenCapKeep], a little bit
> like [hello.you.all.here] ie, dots and ! and spaces are not read as
> delimiter of anything, since they occur inside [ ].
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
> "Robyn H." <RobynH@discussions.microsoft.com> wrote in message
> news:8E996BE3-0098-426E-B726-4F3BAF8CAF8D@microsoft.com...
> > Just a thought -- I'm going to insert the whole SQL statement here and
> > maybe
> > you can find something in the code that would pop out at you as being
> > unusual.
> >
> > SELECT tblTRADES.TradeNo, tblTRADES.TradeDate, tblCLIENTS.ClientName,
> > tblTRADES.TransType,
> > tblTRADES.ExecDTTX+tblTRADES.ExecFinnen+tblTRADES.ExecPost+tblTRADES.ExecAMEX+tblTRADES.ExecSTEPOUT+tblTRADES.ExecOUTSOURCE
> > AS TotalShares, tblTRADES.Security, tblBROKERS.BrokerName,
> > tblTRADES.AvgPrice, tblTRADES.AccountForCredit, tblTRADES.CommRate,
> > tblClientCommKeep.BattenCapKeep, [TotalShares]*[CommRate] AS GrossComm,
> > tblTRADES.ExecDTTX, tblTRADES.ExecFinnen, tblTRADES.ExecPost,
> > tblTRADES.ExecAMEX, tblTRADES.ExecSTEPOUT, tblTRADES.ExecOUTSOURCE,
> > tblTRADES.TicketCharge, tblTRADES.ExecDTTXActual,
> > tblTRADES.ExecFinnenActual,
> > tblTRADES.ExecPostActual, tblTRADES.ExecAMEXActual,
> > IIf([tblTRADES].[AccountForCredit]<>"BAT" Or
> > [tblTRADES].[AccountForCredit]<>"OUTS",([GrossComm]-([tblClientCommKeep.BattenCapKeep]*[TotalShares])),0)
> > AS NetToClient,
> > [GrossComm]-[NetToClient]-[TicketCharge]-[ExecDTTXActual]-[ExecFinnenActual]-[ExecPostActual]-[ExecAMEXActual]
> > AS NetToBroker
> > FROM tblCLIENTS INNER JOIN (tblClientCommKeep INNER JOIN (tblBROKERS INNER
> > JOIN tblTRADES ON tblBROKERS.BrokerID = tblTRADES.Broker) ON
> > tblClientCommKeep.AccountID = tblTRADES.AccountForCredit) ON
> > tblCLIENTS.ClientName = tblTRADES.ClientName
> > WHERE (((tblTRADES.TradeDate) Between [Enter Beginning Date] And [Enter
> > Ending Date]) AND ((tblBROKERS.BrokerName) Like [Enter Broker] & "*")) OR
> > ((([Enter Beginning Date]) Is Null) AND (([Enter Ending Date]) Is Null))
> > OR
> > ((([Enter Beginning Date]) Is Null) AND (([Enter Ending Date]) Is Null)
> > AND
> > (([Enter Broker]) Is Null));
> >
> > any ideas? Much appreciated!
> >
> > "Michel Walsh" wrote:
> >
> >> Hi,
> >>
> >>
> >> Note that the band to dot was not the only modification, I also added a
> >>
> >> OR tblTrades.AccounrForCredit <>
> >>
> >> There is a missing [ around GrossComm in my initial reply,
> >>
> >>
> >> There should be no problem in Access+Jet, but it is preferable to avoid
> >> the
> >> tableName ! fieldname, in SQL (or in a query), since that syntax is
> >> generally not supported elsewhere... and since it does not "cost" a lot
> >> to
> >> use a dot rather than a ! in that case... :-)
> >>
> >>
> >> Hoping it may help,
> >> Vanderghast, Access MVP
> >>
> >>
> >>
> >> "Robyn H." <RobynH@discussions.microsoft.com> wrote in message
> >> news:8A5278D2-9348-4055-ACBC-C3D47754D416@microsoft.com...
> >> > Thanks for the suggestion -- I just tried that and I'm still getting an
> >> > error
> >> > response.
> >> >
> >> > Any other ideas?
> >> >
> >> > PS I had pasted the expression as it read in design view from the
> >> > Expression
> >> > Builder window, not in the SQL view. Is there anything to that?
> >> >
> >> > Thanks!
> >> >
> >> > "Michel Walsh" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >>
> >> >> try:
> >> >>
> >> >> NetToClient:
> >> >> iif( tblTrades.AccountForCredit <> "BAT" OR
> >> >> tblTrades.AccountForCredit <> "OUTS",
> >> >> GrossComm]-([BattenCapKeep]*[TotalShares]), 0 )
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Note: the general syntax is to use a dot, not a bang, in SQL, between
> >> >> the
> >> >> table name and the field.
> >> >>
> >> >>
> >> >>
> >> >> Hoping it may help,
> >> >> Vanderghast, Access MVP
> >> >>
> >> >>
> >> >> "Robyn H." <RobynH@discussions.microsoft.com> wrote in message
> >> >> news:15A88BD9-4CA2-4DA4-8533-E8615193178A@microsoft.com...
> >> >> >I am trying to have a calculation done in a query that would use the
> >> >> >IIF
> >> >> > function to determine what the value will be. The expression below
> >> >> > is
> >> >> > what
> >> >> > I'm using:
> >> >> >
> >> >> > NetToClient: IIf(tblTRADES!AccountForCredit<>"BAT" Or
> >> >> > "OUTS",[GrossComm]-([BattenCapKeep]*[TotalShares]),"0")
> >> >> >
> >> >> > Access is letting me run the query, but I'm getting an #error
> >> >> > message
> >> >> > in
> >> >> > the
> >> >> > cell every time I run it. Does anyone have any ideas as to what
> >> >> > might
> >> >> > be
> >> >> > wrong with the expression?
> >> >> >
> >> >> > Thanks for your help!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
- Next message: Ken Snell [MVP]: "Re: Rounding numbers"
- Previous message: Gary Walter: "Re: Formatting date query Column by week."
- In reply to: Michel Walsh: "Re: IIF Function Question"
- Next in thread: Michel Walsh: "Re: IIF Function Question"
- Reply: Michel Walsh: "Re: IIF Function Question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
