Re: IIF Function Question
From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 09/22/04
- Next message: martin: "delete query"
- Previous message: anonymous_at_discussions.microsoft.com: "sort records by size of items in memo field, or pull those > 255 s"
- In reply to: Robyn H.: "Re: IIF Function Question"
- Next in thread: Robyn H.: "Re: IIF Function Question"
- Reply: Robyn H.: "Re: IIF Function Question"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 22 Sep 2004 16:31:58 -0400
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: martin: "delete query"
- Previous message: anonymous_at_discussions.microsoft.com: "sort records by size of items in memo field, or pull those > 255 s"
- In reply to: Robyn H.: "Re: IIF Function Question"
- Next in thread: Robyn H.: "Re: IIF Function Question"
- Reply: Robyn H.: "Re: IIF Function Question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
